package com.bsoft.gol.hcs.satisfaction.dao;

import cn.hutool.core.util.StrUtil;
import com.bsoft.gol.hcs.common.entity.IdCard;
import com.bsoft.gol.hcs.common.entity.PatientMedicalCard;
import com.bsoft.gol.hcs.specialist.entity.QueryQuestionRecordsResp;
import com.bsoft.gol.hcs.utils.Utils;
import ctd.persistence.annotation.DAOMethod;
import ctd.persistence.annotation.DAOParam;
import ctd.persistence.support.hibernate.template.AbstractHibernateStatelessResultAction;
import ctd.persistence.support.hibernate.template.HibernateSessionTemplate;
import ctd.persistence.support.hibernate.template.HibernateStatelessResultAction;
import ctd.spring.boot.annotation.SsdevDao;
import ctd.util.annotation.RpcService;
import ctd.util.converter.ConversionUtils;
import hcn.bean.specialist.qo.QuerySurveyRuleListRequest;
import hcn.bean.specialist.vo.QuerySurveyRuleQuestionListResponse;
import hcn.util.validation.StringUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.atag.dao.ext.HibernateExtendDAO;
import org.hibernate.Query;
import org.hibernate.StatelessSession;
import org.hibernate.criterion.CriteriaSpecification;
import org.hibernate.transform.Transformers;
import org.springframework.util.CollectionUtils;
import com.bsoft.gol.hcs.satisfaction.bean.request.*;
import com.bsoft.gol.hcs.satisfaction.bean.response.*;
import com.bsoft.gol.hcs.satisfaction.entity.PatientSatisfactionSurvey;
import com.bsoft.gol.hcs.utils.CommonUtils;
import com.bsoft.gol.hcs.utils.DateUtil;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
@Slf4j
@SsdevDao( serviceId = "patientSatisfactionSurveyDAOService")
public abstract class PatientSatisfactionSurveyDAO extends HibernateExtendDAO<PatientSatisfactionSurvey> {

    public PatientSatisfactionSurveyDAO() {
        this.setKeyField("patientSurveyId");
    }

    @DAOMethod(sql = " from PatientSatisfactionSurvey where patientSurveyId =:patientSurveyId ")
    public abstract PatientSatisfactionSurvey getSatisfactionSurveyById(@DAOParam("patientSurveyId") String patientSurveyId);

    @DAOMethod(sql = " update PatientSatisfactionSurvey set processingStatus=:processingStatus where patientSurveyId " +
            "=:patientSurveyId ")
    public abstract void updateProcessingStatusById(@DAOParam("patientSurveyId") String patientSurveyId,
                                                    @DAOParam("processingStatus") String processingStatus);

    @DAOMethod(sql = " update PatientSatisfactionSurvey set processingStatus=:processingStatus,userId=:userId,userName=:userName,initiateTime=now() " +
            "where patientSurveyId =:patientSurveyId ")
    public abstract void updateProcessingStatusAndUserInfoById(@DAOParam("patientSurveyId") String patientSurveyId,
                                                               @DAOParam("processingStatus") String processingStatus,
                                                               @DAOParam("userId") String userId,
                                                               @DAOParam("userName") String userName);

    @DAOMethod(sql = " update PatientSatisfactionSurvey set confirmStatus=:confirmStatus where patientSurveyId " +
            "=:patientSurveyId ")
    public abstract void updateConfirmStatusById(@DAOParam("patientSurveyId") String patientSurveyId,
                                                 @DAOParam("confirmStatus") String confirmStatus);

    /**
     * 根据routeId查询未随访的计划数量
     * @param routeId
     * @return
     */
    @DAOMethod(sql = "select count(1) from  PatientSatisfactionSurvey where survey_rule_id = :routeId and processing_status = 0")
    public abstract Long getRecordsCount(@DAOParam("routeId") Long routeId);

    @DAOMethod(sql = " from PatientSatisfactionSurvey where isNotify='0'  and notifyStatus = '0'  and  surveyStatus = '0' and business_type='2' ")
    public abstract List<PatientSatisfactionSurvey> queryUnsendForuser();

     public List<PatientSatisfactionSurvey> queryUnsendForuserNew(@DAOParam("surveyTime") String surveyTime, @DAOParam("surveytime") String survertime){

         HibernateStatelessResultAction<List<PatientSatisfactionSurvey>> action =
                 new AbstractHibernateStatelessResultAction<List<PatientSatisfactionSurvey>>() {
                     @Override
                     public void execute(StatelessSession statelessSession) throws Exception {
                         StringBuffer stringBuffer = new StringBuffer();
                         stringBuffer.append("select  patient_survey_id as patientSurveyId,business_type as businessType ,business_id as businessId,route_detail_type as routeDetailType,organization_id as organizationId,organization_name as organizationName, department_id as departmentId ,department_name as departmentName ,doctor_id as doctorId,  doctor_name as doctorName ,patient_id as patientId,hospital_number as hospitalNumber ,card_type as cardType ,card_number as  cardNumber ,patient_medical_card_type as patientMedicalCardType ,patient_medical_card_number as patientMedicalCardNumber, patient_name as patientName , patient_birthday as patientBirthday,patient_sex as patientSex , patient_age as patientAge ,telephone as telephone ,address as address , relation as relation ,companion_phone as companionPhone ,companion_name as companionName , questionnaire_id as questionnaireId ,questionnaire_name as questionnaireName,survey_way as surveyWay ,is_notify as isNotify ,notify_time as notifyTime ,processing_status as processingStatus , creator_id as creatorId , gmt_create as  gmtCreate ,modify_id as modifyId,gmt_modified  as gmtModified ,survey_status as surveyStatus ,plan_survey_time as planSurveyTime ,plan_time as planTime ,notify_status as notifyStatus,survey_time as surveyTime ,survey_id as surveyId ,survey_name  as surveyName ,survey_score as surveyScore ,survey_rule_id as surveyRuleId ,survey_rule_name as surveyRuleName ,confirm_status as confirmStatus ,user_id as userId ,user_name as userName ,initiate_time as initiateTime ,survey_source as surveySource from patient_satisfaction_survey  where is_notify = '0' and notify_status= '0' and plan_survey_time"+
                                 " <= :surveyTime and  plan_survey_time >= :survertime  and survey_status = '0' and business_type='2'  ORDER BY RAND() "
                         );
                         Query query=   statelessSession.createSQLQuery(stringBuffer.toString());
                         query.setParameter("surveyTime",surveyTime);
                         query.setParameter("survertime",survertime);
                         query.setMaxResults(500);
                         query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
                         List<Map> list = query.list();
                         List<PatientSatisfactionSurvey> voList = new ArrayList<>();
                         if (CommonUtils.isNotEmpty(list)){
                             for (Map map: list ) {
                                 voList.add(ConversionUtils.convert(map,PatientSatisfactionSurvey.class));
                             }
                         }
                         setResult(voList);
                     }
                 };

         HibernateSessionTemplate.instance().execute(action);
         return  action.getResult();
     }


    /**
     * @param surveyTime
     * @throws
     * @description: 查询没有发送的计划记录
     * @return: java.util.List<satisfaction.entity.PatientSatisfactionSurvey>
     * @author: tongtp
     * @date: 2020/9/3 16:37
     */
//    @DAOMethod(sql = " from PatientSatisfactionSurvey where isNotify = '0' and notifyStatus = '0' and planSurveyTime" +
//            " <= :surveyTime and surveyStatus = '0' ORDER BY RAND() ")
    public List<PatientSatisfactionSurvey> queryUnsend(@DAOParam("surveyTime") String surveyTime, @DAOParam("surveytime") String survertime){

        HibernateStatelessResultAction<List<PatientSatisfactionSurvey>> action =
                new AbstractHibernateStatelessResultAction<List<PatientSatisfactionSurvey>>() {
                    @Override
                    public void execute(StatelessSession statelessSession) throws Exception {
                        StringBuffer stringBuffer = new StringBuffer();
                        stringBuffer.append("select  patient_survey_id as patientSurveyId,business_type as businessType ,business_id as businessId,route_detail_type as routeDetailType,organization_id as organizationId,organization_name as organizationName, department_id as departmentId ,department_name as departmentName ,doctor_id as doctorId,  doctor_name as doctorName ,patient_id as patientId,hospital_number as hospitalNumber ,card_type as cardType ,card_number as  cardNumber ,patient_medical_card_type as patientMedicalCardType ,patient_medical_card_number as patientMedicalCardNumber, patient_name as patientName , patient_birthday as patientBirthday,patient_sex as patientSex , patient_age as patientAge ,telephone as telephone ,address as address , relation as relation ,companion_phone as companionPhone ,companion_name as companionName , questionnaire_id as questionnaireId ,questionnaire_name as questionnaireName,survey_way as surveyWay ,is_notify as isNotify ,notify_time as notifyTime ,processing_status as processingStatus , creator_id as creatorId , gmt_create as  gmtCreate ,modify_id as modifyId,gmt_modified  as gmtModified ,survey_status as surveyStatus ,plan_survey_time as planSurveyTime ,plan_time as planTime ,notify_status as notifyStatus,survey_time as surveyTime ,survey_id as surveyId ,survey_name  as surveyName ,survey_score as surveyScore ,survey_rule_id as surveyRuleId ,survey_rule_name as surveyRuleName ,confirm_status as confirmStatus ,user_id as userId ,user_name as userName ,initiate_time as initiateTime ,survey_source as surveySource from patient_satisfaction_survey  where is_notify = '0' and notify_status= '0' and plan_survey_time"+
                                " <= :surveyTime and  plan_survey_time >= :survertime  and survey_status = '0' ORDER BY RAND() "
                        );
                        Query query=   statelessSession.createSQLQuery(stringBuffer.toString());
                        query.setParameter("surveyTime",surveyTime);
                        query.setParameter("survertime",survertime);
                        query.setMaxResults(2000);
                        query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
                        List<Map> list = query.list();
                        List<PatientSatisfactionSurvey> voList = new ArrayList<>();
                        if (CommonUtils.isNotEmpty(list)){
                            for (Map map: list ) {
                                voList.add(ConversionUtils.convert(map,PatientSatisfactionSurvey.class));
                            }
                        }
                        setResult(voList);
                    }
                };

        HibernateSessionTemplate.instance().execute(action);
        return  action.getResult();

    }


    /**
     * @param userInfoId
     * @throws
     * @description: 根据问卷答案Id查询满意度调查计划
     * @return: satisfaction.entity.PatientSatisfactionSurvey
     * @author: tongtp
     * @date: 2020/9/3 8:55
     */
    @DAOMethod(sql = " from PatientSatisfactionSurvey where userInfoId = :userInfoId")
    public abstract PatientSatisfactionSurvey getByUserInfoId(@DAOParam("userInfoId") String userInfoId);

    public List<SurveyPlanDeptListResponse> querySurveyDeptByDeptId(final QuerySurveyDeptListRequest req) {
        HibernateStatelessResultAction<List<SurveyPlanDeptListResponse>> action =
                new AbstractHibernateStatelessResultAction<List<SurveyPlanDeptListResponse>>() {
                    @Override
                    public void execute(StatelessSession ss) {
                        StringBuffer stringBuffer = new StringBuffer();
                        stringBuffer.append(" SELECT\n" +
                                "  t.questionnaire_id,\n" +
                                "  t.department_id AS departmentId,\n" +
                                "  t.department_name AS departmentName,\n" +
                                "  t.survey_status AS push,\n" +
                                "  f.survey_status1 AS back,\n" +
                                "  IFNULL(h.abc,0) / f.survey_status1 AS c,\n" +
                                "  t.type\n" +
                                "FROM\n" +
                                "  (\n" +
                                "    SELECT\n" +
                                "      questionnaire_id,\n" +
                                "      department_id,\n" +
                                "      department_name,\n" +
                                "      CASE\n" +
                                "    WHEN INSTR(department_name, '东院') > 0 THEN\n" +
                                "      '1'\n" +
                                "    ELSE\n" +
                                "      '2'\n" +
                                "    END AS 'type',\n" +
                                "    count(survey_status) AS survey_status\n" +
                                "  FROM\n" +
                                "    patient_satisfaction_survey\n" +
                                "  WHERE\n" +
                                "    department_id IS NOT NULL\n" +
                                "  AND notify_status = 1 ");

                        if (StringUtils.isNotEmpty(req.getBeginDate())) {
                            stringBuffer.append("and notify_time>=:beginDate  ");
                        }
                        if (StringUtils.isNotEmpty(req.getEndDate())) {
                            stringBuffer.append("and notify_time<=:endDate  ");
                        }

                        stringBuffer.append(" GROUP BY\n" +
                                "    department_id\n" +
                                "  ) t\n" +
                                "LEFT JOIN (\n" +
                                "  SELECT\n" +
                                "    questionnaire_id,\n" +
                                "    department_id,\n" +
                                "    department_name,\n" +
                                "    count(survey_status) AS survey_status1\n" +
                                "  FROM\n" +
                                "    patient_satisfaction_survey\n" +
                                "  WHERE\n" +
                                "    department_id IS NOT NULL\n" +
                                "  AND survey_status = 1 ");

                        if (StringUtils.isNotEmpty(req.getBeginDate())) {
                            stringBuffer.append(" and notify_time>=:beginDate  ");
                        }
                        if (StringUtils.isNotEmpty(req.getEndDate())) {
                            stringBuffer.append(" and notify_time<=:endDate  ");
                        }

                        stringBuffer.append("  GROUP BY\n" +
                                "    department_id\n" +
                                ") f ON t.department_id = f.department_id\n" +
                                "LEFT JOIN (\n" +
                                "  SELECT\n" +
                                "    count(*) abc,\n" +
                                "    department_id\n" +
                                "  FROM\n" +
                                "    patient_satisfaction_survey\n" +
                                "  WHERE\n" +
                                "    1 = 1\n" +
                                "    and department_id IS NOT NULL\n" +
                                "   and survey_status = 1\n" +
                                "  AND survey_score >= 8\n" +
                                "  AND survey_score <= 10   ");

                        if (StringUtils.isNotEmpty(req.getBeginDate())) {
                            stringBuffer.append(" and notify_time>=:beginDate  ");
                        }
                        if (StringUtils.isNotEmpty(req.getEndDate())) {
                            stringBuffer.append(" and notify_time<=:endDate  ");
                        }
                        if (CommonUtils.isNotEmpty(req.getQuestionnaireId())) {
                            stringBuffer.append(" and questionnaire_id =:questionnaire_id  ");
                        }

                        stringBuffer.append(" GROUP BY\n" +
                                "    department_id\n" +
                                ") h ON t.department_id = h.department_id\n" +
                                "WHERE\n" +
                                "  1 = 1 ");


                        if (CommonUtils.isNotEmpty(req.getQuestionnaireId())) {
                            stringBuffer.append(" and t.questionnaire_id =:questionnaire_id  ");
                        }

                        stringBuffer.append( " ORDER BY type, c ");
                        Query sqlQuery = ss.createSQLQuery(stringBuffer.toString());

                        if (CommonUtils.isNotEmpty(req.getDeptId())) {
                            sqlQuery.setParameter("department_id", req.getDeptId());
                        }

                        if (StringUtils.isNotEmpty(req.getBeginDate())) {
                            sqlQuery.setParameter("beginDate", req.getBeginDate() + " 00:00:00");
                        }
                        if (StringUtils.isNotEmpty(req.getEndDate())) {
                            sqlQuery.setParameter("endDate", req.getEndDate() + " 23:59:59");
                        }
                        if (CommonUtils.isNotEmpty(req.getQuestionnaireId())) {
                            sqlQuery.setParameter("questionnaire_id", req.getQuestionnaireId());
                        }

                        sqlQuery.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);

                        List<Map> list = sqlQuery.list();
                        List<SurveyPlanDeptListResponse> voList = new ArrayList<>();
                        if (CommonUtils.isNotEmpty(list)) {
                            for (Map map : list) {
                                voList.add(ConversionUtils.convert(map, SurveyPlanDeptListResponse.class));
                            }
                        } else {
                            voList = new ArrayList<>();
                        }
                        setResult(voList);
                    }
                };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }


    public List<SurveyPlanTitleListResponse> querySurveyAverByDeptId(final QuerySurveyDeptListRequest req) {
        HibernateStatelessResultAction<List<SurveyPlanTitleListResponse>> action =
                new AbstractHibernateStatelessResultAction<List<SurveyPlanTitleListResponse>>() {
                    @Override
                    public void execute(StatelessSession ss) {
                        StringBuffer stringBuffer = new StringBuffer();
                        stringBuffer.append(" select a.questionnaire_id,a.title_id as titleId,a.title_name as titleName,AVG(a.answer_name) as average,a.order_num as orderNum from   " +
                                " (select p.questionnaire_id,p.patient_survey_id,p.title_id,p.title_name,p.answer_id,p.answer_name,p.order_num from  " +
                                " (select patient_survey_id from  patient_satisfaction_survey where department_id =:department_id and survey_status ='1' ) t LEFT JOIN  " +
                                " (select questionnaire_id,patient_survey_id,title_id,title_name,answer_id,answer_name,order_num from patient_satisfaction_survey_answer where widget_type ='699' ");

                        if (StringUtils.isNotEmpty(req.getBeginDate())) {
                            stringBuffer.append("and gmt_create>=:beginDate  ");
                        }
                        if (StringUtils.isNotEmpty(req.getEndDate())) {
                            stringBuffer.append("and gmt_create<=:endDate  ");
                        }

                        stringBuffer.append("  ) p on p.patient_survey_id = t.patient_survey_id where p.patient_survey_id is not null) a  where 1=1  " );

                        if (CommonUtils.isNotEmpty(req.getQuestionnaireId())) {
                            stringBuffer.append(" and a.questionnaire_id =:questionnaire_id  ");
                        }

                        stringBuffer.append( " GROUP BY a.title_id ORDER BY a.order_num ");
                        Query sqlQuery = ss.createSQLQuery(stringBuffer.toString());

                        sqlQuery.setParameter("department_id", req.getDeptId());

                        if (CommonUtils.isNotEmpty(req.getQuestionnaireId())) {
                            sqlQuery.setParameter("questionnaire_id", req.getQuestionnaireId());
                        }

                        if (StringUtils.isNotEmpty(req.getBeginDate())) {
                            sqlQuery.setParameter("beginDate", req.getBeginDate() + " 00:00:00");
                        }
                        if (StringUtils.isNotEmpty(req.getEndDate())) {
                            sqlQuery.setParameter("endDate", req.getEndDate() + " 23:59:59");
                        }

                        sqlQuery.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);

                        List<Map> list = sqlQuery.list();
                        List<SurveyPlanTitleListResponse> voList = new ArrayList<>();
                        if (CommonUtils.isNotEmpty(list)) {
                            for (Map map : list) {
                                voList.add(ConversionUtils.convert(map, SurveyPlanTitleListResponse.class));
                            }
                        } else {
                            voList = new ArrayList<>();
                        }
                        setResult(voList);
                    }
                };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }


    public Integer queryDcotrorCount(String doctorId, String beginDate,String endDate ,String questionnaireId ){
        HibernateStatelessResultAction<Integer> action = new AbstractHibernateStatelessResultAction<Integer>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer stringBuffer = new StringBuffer();
                stringBuffer.append("select count(1) as total from patient_satisfaction_survey where survey_status =1 and survey_score>=8 and survey_score<=10");
                if (StringUtils.isNotEmpty(doctorId)){
                    stringBuffer.append(" and doctor_id=:doctor_id ");
                }
                if (StringUtils.isNotEmpty(beginDate)){
                    stringBuffer.append("and notify_time>=:beginDate  ");
                }
                if (StringUtils.isNotEmpty(endDate)){
                    stringBuffer.append("and notify_time<=:endDate  ");
                }
                if (StringUtils.isNotEmpty(questionnaireId)){
                    stringBuffer.append("and questionnaire_id=:questionnaire_id ");
                }
                Query sqlQuery = ss.createSQLQuery(stringBuffer.toString());
                if (StringUtils.isNotEmpty(doctorId)){
                    sqlQuery.setParameter("doctor_id", doctorId);
                }
                if (StringUtils.isNotEmpty(beginDate)){
                    sqlQuery.setParameter("beginDate", beginDate + " 00:00:00");
                }
                if (StringUtils.isNotEmpty(endDate)){
                    sqlQuery.setParameter("endDate", endDate + " 23:59:59");
                }
                if (StringUtils.isNotEmpty(questionnaireId)){
                    sqlQuery.setParameter("questionnaire_id", questionnaireId);
                }
                sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
                List<Map<String, Object>> dbList = sqlQuery.list();
                Object total = dbList.get(0).get("total");
                Integer bigInteger = new Integer(String.valueOf(total));
                setResult(bigInteger);
            }

        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    public List<SurveyPlanDeptListResponse> queryDoctorWorkList1(final QuerySurveyDeptListRequest req) {
        HibernateStatelessResultAction<List<SurveyPlanDeptListResponse>> action =
                new AbstractHibernateStatelessResultAction<List<SurveyPlanDeptListResponse>>() {
                    @Override
                    public void execute(StatelessSession ss) {
                        StringBuffer stringBuffer = new StringBuffer();
                        stringBuffer.append("SELECT\n" +
                                "\tt.questionnaire_id,\n" +
                                "\tt.doctor_id AS doctorId,\n" +
                                "\tt.doctor_name AS doctorName,\n" +
                                "\tt.survey_status AS push ,\n" +
                                "\tf.survey_status1 AS back,\n" +
                                "\t#t.survey_status / f.survey_status1 AS c,\n" +
                                "\tIFNULL(h.abc, 0) / f.survey_status1 AS c\n" +
                                "FROM\n" +
                                "\t(\n" +
                                "\t\tSELECT\n" +
                                "\t\t\tquestionnaire_id,\n" +
                                "\t\t\tdoctor_id,\n" +
                                "\t\t\tdoctor_name,\n" +
                                "\t\t\tcount(survey_status) AS survey_status\n" +
                                "\t\tFROM\n" +
                                "\t\t\tpatient_satisfaction_survey\n" +
                                "\t\tWHERE\n" +
                                "\t\t\tdoctor_id IS NOT NULL ");
                        if(StringUtils.isNotEmpty(req.getDoctorName())){
                            stringBuffer.append("and doctor_name=:doctor_name ");
                        }
                        if (StringUtils.isNotEmpty(req.getBeginDate())) {
                            stringBuffer.append("and notify_time>=:beginDate  ");
                        }
                        if (StringUtils.isNotEmpty(req.getEndDate())) {
                            stringBuffer.append("and notify_time<=:endDate  ");
                        }
                        if (StringUtils.isNotEmpty(req.getQuestionnaireId())){
                            stringBuffer.append("and questionnaire_id=:questionnaire_id ");
                        }
//                        if (StringUtils.isNotEmpty(req.getDoctorName())){
//                            stringBuffer.append("and doctor_name=:doctor_name ");
//                        }
                        stringBuffer.append(" GROUP BY\n" +
                                "\t\t\tdoctor_id\n" +
                                "\t) t\n" +
                                "LEFT JOIN (\n" +
                                "\tSELECT\n" +
                                "\t\tquestionnaire_id,\n" +
                                "\t\tdoctor_id,\n" +
                                "\t\tdoctor_name,\n" +
                                "\t\tcount(survey_status) AS survey_status1\n" +
                                "\tFROM\n" +
                                "\t\tpatient_satisfaction_survey\n" +
                                "\tWHERE\n" +
                                "\t\tdoctor_id IS NOT NULL\n" +
                                "  AND survey_status = 1 ");


                        if (StringUtils.isNotEmpty(req.getBeginDate())) {
                            stringBuffer.append("and notify_time>=:beginDate  ");
                        }
                        if (StringUtils.isNotEmpty(req.getEndDate())) {
                            stringBuffer.append("and notify_time<=:endDate  ");
                        }
                        if (StringUtils.isNotEmpty(req.getQuestionnaireId())){
                            stringBuffer.append("and questionnaire_id=:questionnaire_id  ");
                        }

                        stringBuffer.append(" GROUP BY\n" +
                                "\t\tdoctor_id\n" +
                                ") f ON t.doctor_id = f.doctor_id\n" +
                                "LEFT JOIN (\n" +
                                "\tSELECT\n" +
                                "\t\tcount(*) abc,\n" +
                                "\t\tdoctor_id\n" +
                                "\tFROM\n" +
                                "\t\tpatient_satisfaction_survey\n" +
                                "\tWHERE\n" +
                                "\t\t1 = 1 #and survey_status = 1\n" +
                                "\tAND survey_score >= 8\n" +
                                "\tAND survey_score <= 10  ");
                        if (StringUtils.isNotEmpty(req.getBeginDate())) {
                            stringBuffer.append("and notify_time>=:beginDate  ");
                        }
                        if (StringUtils.isNotEmpty(req.getEndDate())) {
                            stringBuffer.append("and notify_time<=:endDate  ");
                        }
                        if (StringUtils.isNotEmpty(req.getQuestionnaireId())) {
                            stringBuffer.append("and questionnaire_id=:questionnaire_id  ");

                        }
                        stringBuffer.append("GROUP BY\n" +
                                "\t\tdoctor_id\n" +
                                ") h ON t.doctor_id = h.doctor_id\n" +
                                "WHERE\n" +
                                "\t1 = 1 ");
                        if (StringUtils.isNotEmpty(req.getQuestionnaireId())){
                            stringBuffer.append("and t.questionnaire_id=:questionnaire_id  ");
                        }
                         stringBuffer.append("ORDER BY\n" +
                                 "\tc ");


                        Query sqlQuery = ss.createSQLQuery(stringBuffer.toString());

//                        if (CommonUtils.isNotEmpty(req.getDoctorId())) {
//                            sqlQuery.setParameter("doctor_id", req.getDoctorId());
//                        }
//                        if (CommonUtils.isNotEmpty(req.getDeptId())) {
////                            String trim = Utils.trim(StrUtil.join(",", req.getNewDeptId()).replaceAll("\\b", "'"), ",");
////                            log.info("==============>"+trim);
//                            sqlQuery.setParameterList("department_id", req.getNewDeptId());
//                        }

                        if (StringUtils.isNotEmpty(req.getBeginDate())) {
                            sqlQuery.setParameter("beginDate", req.getBeginDate() + " 00:00:00");
                        }
                        if (StringUtils.isNotEmpty(req.getEndDate())) {
                            sqlQuery.setParameter("endDate", req.getEndDate() + " 23:59:59");
                        }
                        if (CommonUtils.isNotEmpty(req.getQuestionnaireId())) {
                            sqlQuery.setParameter("questionnaire_id", req.getQuestionnaireId());
                        }
                        if (CommonUtils.isNotEmpty(req.getDoctorName())){
                            sqlQuery.setParameter("doctor_name",req.getDoctorName());
                        }
//                        if (CommonUtils.isNotEmpty(req.getDoctorName())){
//                            sqlQuery.setParameter("doctor_name",req.getDoctorName());
//                        }
                        log.info("-----sqlQuery-------"+sqlQuery);
                        sqlQuery.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);

                        List<Map> list = sqlQuery.list();
                        List<SurveyPlanDeptListResponse> voList = new ArrayList<>();
                        if (CommonUtils.isNotEmpty(list)) {
                            for (Map map : list) {
                                voList.add(ConversionUtils.convert(map, SurveyPlanDeptListResponse.class));
                            }
                        } else {
                            voList = new ArrayList<>();
                        }
                        setResult(voList);
                    }
                };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }



    public List<SurveyPlanDeptListResponse> queryDoctorWorkList(final QuerySurveyDeptListRequest req) {
        HibernateStatelessResultAction<List<SurveyPlanDeptListResponse>> action =
                new AbstractHibernateStatelessResultAction<List<SurveyPlanDeptListResponse>>() {
                    @Override
                    public void execute(StatelessSession ss) {
                        StringBuffer stringBuffer = new StringBuffer();
                        stringBuffer.append("select t.questionnaire_id,t.doctor_id as doctorId,t.doctor_name as doctorName,t.survey_status as back,f.survey_status1 as push,t.survey_status/f.survey_status1 as c, t.type  from (  " +
                                "select questionnaire_id,doctor_id,doctor_name,CASE WHEN INSTR(department_name, \"东院\" ) > 0 THEN '1' ELSE '2' END AS 'type' , count(survey_status) as survey_status " +
                                "from patient_satisfaction_survey where doctor_id is not null and survey_status =1  ");
                        log.info("CommonUtils.isNotEmpty------------------->"+CommonUtils.isNotEmpty(req.getNewDeptId()));
                        log.info("---------------------------->"+(req.getNewDeptId()!=null && !req.getNewDeptId().isEmpty()));
                        if (StringUtils.isNotEmpty(req.getDeptIds())) {
                            stringBuffer.append(" and department_id in:department_id ");
                        }

                        if (StringUtils.isNotEmpty(req.getBeginDate())) {
                            stringBuffer.append("and notify_time>=:beginDate  ");
                        }
                        if (StringUtils.isNotEmpty(req.getEndDate())) {
                            stringBuffer.append("and notify_time<=:endDate  ");
                        }
                        if (StringUtils.isNotEmpty(req.getDoctorName())){
                            stringBuffer.append("and doctor_name=:doctor_name ");
                        }

                        stringBuffer.append("GROUP BY doctor_id) t LEFT JOIN  " +
                                "(select questionnaire_id,doctor_id,doctor_name,count(survey_status) as survey_status1  " +
                                "from patient_satisfaction_survey where doctor_id is not null   ");

                        if (StringUtils.isNotEmpty(req.getDeptIds())) {
                            log.info("-------NewDeptId-----------"+req.getNewDeptId());
                            stringBuffer.append(" and department_id in :department_id ");
                        }

                        if (StringUtils.isNotEmpty(req.getBeginDate())) {
                            stringBuffer.append(" and notify_time>=:beginDate  ");
                        }
                        if (StringUtils.isNotEmpty(req.getEndDate())) {
                            stringBuffer.append(" and notify_time<=:endDate  ");
                        }
                        if(StringUtils.isNotEmpty(req.getDoctorName())){
                            stringBuffer.append("and doctor_name=:doctor_name ");
                        }

                        stringBuffer.append(" GROUP BY doctor_id) f on t.doctor_id = f.doctor_id  where 1=1   ");
                        if (CommonUtils.isNotEmpty(req.getDoctorId())) {
                            stringBuffer.append(" and t.doctor_id =:doctor_id ");
                        }

                        if (CommonUtils.isNotEmpty(req.getQuestionnaireId())) {
                            stringBuffer.append(" and t.questionnaire_id =:questionnaire_id  ");
                        }

                        stringBuffer.append( " ORDER BY type, c ");

                        Query sqlQuery = ss.createSQLQuery(stringBuffer.toString());

                        if (CommonUtils.isNotEmpty(req.getDoctorId())) {
                            sqlQuery.setParameter("doctor_id", req.getDoctorId());
                        }
                        if (CommonUtils.isNotEmpty(req.getDeptId())) {
//                            String trim = Utils.trim(StrUtil.join(",", req.getNewDeptId()).replaceAll("\\b", "'"), ",");
//                            log.info("==============>"+trim);
                            sqlQuery.setParameterList("department_id", req.getNewDeptId());
                        }

                        if (StringUtils.isNotEmpty(req.getBeginDate())) {
                            sqlQuery.setParameter("beginDate", req.getBeginDate() + " 00:00:00");
                        }
                        if (StringUtils.isNotEmpty(req.getEndDate())) {
                            sqlQuery.setParameter("endDate", req.getEndDate() + " 23:59:59");
                        }
                        if (CommonUtils.isNotEmpty(req.getQuestionnaireId())) {
                            sqlQuery.setParameter("questionnaire_id", req.getQuestionnaireId());
                        }
                        if (CommonUtils.isNotEmpty(req.getDoctorName())){
                            sqlQuery.setParameter("doctor_name",req.getDoctorName());
                        }
                        log.info("-----sqlQuery-------"+sqlQuery);
                        sqlQuery.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);

                        List<Map> list = sqlQuery.list();
                        List<SurveyPlanDeptListResponse> voList = new ArrayList<>();
                        if (CommonUtils.isNotEmpty(list)) {
                            for (Map map : list) {
                                voList.add(ConversionUtils.convert(map, SurveyPlanDeptListResponse.class));
                            }
                        } else {
                            voList = new ArrayList<>();
                        }
                        setResult(voList);
                    }
                };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    public List<SurveyPlanTitleListResponse> querySurveyAverByDoctorId(final QuerySurveyDeptListRequest req) {
        HibernateStatelessResultAction<List<SurveyPlanTitleListResponse>> action =
                new AbstractHibernateStatelessResultAction<List<SurveyPlanTitleListResponse>>() {
                    @Override
                    public void execute(StatelessSession ss) {
                        StringBuffer stringBuffer = new StringBuffer();
                        stringBuffer.append(" select a.questionnaire_id,a.title_id as titleId,a.title_name as titleName, case when AVG( a.answer_name) is null then 0 else AVG( a.answer_name) end  AS average ,a.order_num as orderNum from   " +
                                " (select p.questionnaire_id,p.patient_survey_id,p.title_id,p.title_name,p.answer_id,p.answer_name,p.order_num from  " +
                                " (select patient_survey_id from  patient_satisfaction_survey where doctor_id =:doctor_id and survey_status ='1' ) t LEFT JOIN  " +
                                " (select questionnaire_id,patient_survey_id,title_id,title_name,answer_id,answer_name,order_num from patient_satisfaction_survey_answer where widget_type ='699' ");

                        if (StringUtils.isNotEmpty(req.getBeginDate())) {
                            stringBuffer.append("and gmt_create>=:beginDate  ");
                        }
                        if (StringUtils.isNotEmpty(req.getEndDate())) {
                            stringBuffer.append("and gmt_create<=:endDate  ");
                        }

                        stringBuffer.append("  ) p on p.patient_survey_id = t.patient_survey_id where p.patient_survey_id is not null) a  where 1=1  " );

                        if (CommonUtils.isNotEmpty(req.getQuestionnaireId())) {
                            stringBuffer.append(" and a.questionnaire_id =:questionnaire_id  ");
                        }

                        stringBuffer.append( " GROUP BY a.title_id ORDER BY a.order_num ");
                        Query sqlQuery = ss.createSQLQuery(stringBuffer.toString());

                        sqlQuery.setParameter("doctor_id", req.getDoctorId());

                        if (CommonUtils.isNotEmpty(req.getQuestionnaireId())) {
                            sqlQuery.setParameter("questionnaire_id", req.getQuestionnaireId());
                        }

                        if (StringUtils.isNotEmpty(req.getBeginDate())) {
                            sqlQuery.setParameter("beginDate", req.getBeginDate() + " 00:00:00");
                        }
                        if (StringUtils.isNotEmpty(req.getEndDate())) {
                            sqlQuery.setParameter("endDate", req.getEndDate() + " 23:59:59");
                        }

                        sqlQuery.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);

                        List<Map> list = sqlQuery.list();
                        List<SurveyPlanTitleListResponse> voList = new ArrayList<>();
                        if (CommonUtils.isNotEmpty(list)) {
                            for (Map map : list) {
                                voList.add(ConversionUtils.convert(map, SurveyPlanTitleListResponse.class));
                            }
                        } else {
                            voList = new ArrayList<>();
                        }
                        setResult(voList);
                    }
                };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }


    public List<SurveyPlanDetailResponse.TitleBean> queryTeamSurveyRecordList(final SurveyPlanDetailRequest request) {
        HibernateStatelessResultAction<List<SurveyPlanDetailResponse.TitleBean>> action = new AbstractHibernateStatelessResultAction<List<SurveyPlanDetailResponse.TitleBean>>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer sql = new StringBuffer();
                sql.append("select t.title_id as titleId,t.title_name as titleName,t.answer_id as answerId,t.answer_name as answerName,");
                sql.append("t.medical_team_id as medicalTeamId,t.medical_team_name as medicalTeamName,p.processing_opinions as processingOpinions,");
                sql.append("p.processing_time as processingTime,p.notify_flag as notifyFlag,p.questionnaire_id as questionnaireId,p.questionnaire_name as questionnaireName ");
                sql.append("from team_survey_record t ");
                sql.append("left join patient_satisfaction_survey_answer p on t.patient_survey_id=p.patient_survey_id ");
                sql.append("where t.patient_survey_id =:patientSurveyId and (p.processing_status='1' OR p.processing_status='2') ");
                sql.append("and t.questionnaire_id=p.questionnaire_id and t.title_id=p.title_id ");
                Query query = ss.createSQLQuery(sql.toString());
                query.setParameter("patientSurveyId", request.getPatientSurveyId());
                query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                List<SurveyPlanDetailResponse.TitleBean> titleBeanList = new ArrayList<SurveyPlanDetailResponse.TitleBean>();
                List<Map> list = query.list();
                if (list.size() > 0) {
                    for (Map map : list) {
                        titleBeanList.add(ConversionUtils.convert(map, SurveyPlanDetailResponse.TitleBean.class));
                    }
                    setResult(titleBeanList);
                } else {
                    setResult(null);
                }
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    public List<SurveyPlanListResponse.SurveyPlanBean> querySatisfactionSurveyPlanList(final SurveyPlanListRequest request) {
        HibernateStatelessResultAction<List<SurveyPlanListResponse.SurveyPlanBean>> action = new AbstractHibernateStatelessResultAction<List<SurveyPlanListResponse.SurveyPlanBean>>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer sql = new StringBuffer();
                sql.append("select r.form_id as formId, p.patient_survey_id as patientSurveyId,p.patient_name as patientName,");
                sql.append("p.patient_age as patientAge,p.patient_sex as patientSex,r.questionnaire_id as questionnaireId,r.questionnaire_name AS questionnaireName,p.initiate_time as initiateTime,p.plan_survey_time as planSurveyTime,");
                sql.append("p.department_id as departmentId,p.department_name as departmentName,p.telephone,p.notify_time as notifyTime,p.survey_status as surveyStatus,r.survey_status as formStatus, ");
                sql.append("p.processing_status as processingStatus,p.business_type as recordType,p.business_id as businessId,");
                sql.append("p.companion_phone as contactTelephone,p.companion_name as contactPersonName,r.user_info_id as userInfoId , p.survey_way as surveyWay , p.relation as relation ,p.notify_status as notifyStatus ");
                sql.append("from patient_satisfaction_survey p ");
                sql.append(" inner join satisfaction_requisition_record r on p.patient_survey_id=r.record_id ");

                if(request.getQueryType()==2){
                    sql.append("inner join patient_satisfaction_survey_answer a on p.patient_survey_id=a.patient_survey_id ");
                }
                sql.append("where 1=1  ");

                Query query = ss.createSQLQuery(getSatisfactionSurveySql(sql, request).toString());
                if (StringUtils.isNotEmpty(request.getSelectedZxksId())) {
                    query.setParameter("selectedZxksId", request.getSelectedZxksId());
                }
                if (StringUtils.isNotEmpty(request.getSelectedZxjg())) {
                    query.setParameter("selectedZxjg", request.getSelectedZxjg());
                }
                if (StringUtils.isNotEmpty(request.getQuestionnaireId())) {
                    query.setParameter("questionnaireId", request.getQuestionnaireId());
                }
                if (StringUtils.isNotEmpty(request.getSurveyStatus())) {
                    query.setParameter("surveyStatus", request.getSurveyStatus());
                }
                if (StringUtils.isNotEmpty(request.getFormStatus())) {
                    query.setParameter("formStatus", request.getFormStatus());
                }
                if (StringUtils.isNotEmpty(request.getProcessingStatus())) {
                    query.setParameter("processingStatus", request.getProcessingStatus());
                }
                if (StringUtils.isNotEmpty(request.getSelectedMaxZf())) {
                    query.setParameter("selectedMaxZf", new BigDecimal(request.getSelectedMaxZf()));
                }
                if (StringUtils.isNotEmpty(request.getSelectedMinZf())){
                    query.setParameter("selectedMinZf", new BigDecimal(request.getSelectedMinZf()));
                }
                if (StringUtils.isNotEmpty(request.getCardType())) {
                    query.setParameter("cardType", request.getCardType());
                }
                if (StringUtils.isNotEmpty(request.getCardNumber())) {
                    query.setParameter("cardNumber", request.getCardNumber());
                }
                if(StringUtils.isNotEmpty(request.getSurveyWay())){
                    query.setParameter("surveyWay",request.getSurveyWay());
                } if (StringUtils.isNotEmpty(request.getRelation())){
                    query.setParameter("relation",request.getRelation());
                }  if (StringUtils.isNotEmpty(request.getNotifyStatus())){
                    query.setParameter("notifyStatus",request.getNotifyStatus());
                }   if (StringUtils.isNotEmpty(request.getDateStart())){
                     query.setParameter("dateStart",request.getDateStart());
                } if (StringUtils.isNotEmpty(request.getDateEnd())){
                    query.setParameter("dateEnd",request.getDateEnd());
                }

                if(request.getQueryType()==1){
                    query.setFirstResult((request.getPageNo() - 1) * request.getPageSize());
                    query.setMaxResults(request.getPageSize());
                }
                query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                List<SurveyPlanListResponse.SurveyPlanBean> followUpPlanList = new ArrayList<SurveyPlanListResponse.SurveyPlanBean>();
                List<Map> list = query.list();
                if (list.size() > 0) {
                    for (Map map : list) {
                        followUpPlanList.add(ConversionUtils.convert(map, SurveyPlanListResponse.SurveyPlanBean.class));
                    }
                    setResult(followUpPlanList);
                } else {
                    setResult(null);
                }
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    public BigInteger getSatisfactionSurveyPlanCount(final SurveyPlanListRequest request) {
        HibernateStatelessResultAction<BigInteger> action = new AbstractHibernateStatelessResultAction<BigInteger>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer sql = new StringBuffer();
                sql.append("select count(1) ");
                sql.append("from patient_satisfaction_survey p ");
                sql.append(" inner join satisfaction_requisition_record r on p.patient_survey_id=r.record_id ");
                sql.append("where 1=1 ");
                Query query = ss.createSQLQuery(getSatisfactionSurveySql1(sql, request).toString());
                if (StringUtils.isNotEmpty(request.getSelectedZxksId())) {
                    query.setParameter("selectedZxksId", request.getSelectedZxksId());
                }
                if (StringUtils.isNotEmpty(request.getSelectedZxjg())) {
                    query.setParameter("selectedZxjg", request.getSelectedZxjg());
                }
                if (StringUtils.isNotEmpty(request.getQuestionnaireId())) {
                    query.setParameter("questionnaireId", request.getQuestionnaireId());
                }
                if (StringUtils.isNotEmpty(request.getSurveyStatus())) {
                    query.setParameter("surveyStatus", request.getSurveyStatus());
                }
                if (StringUtils.isNotEmpty(request.getFormStatus())) {
                    query.setParameter("formStatus", request.getFormStatus());
                }
                if (StringUtils.isNotEmpty(request.getProcessingStatus())) {
                    query.setParameter("processingStatus", request.getProcessingStatus());
                }
                if (StringUtils.isNotEmpty(request.getSelectedMaxZf())) {
                    query.setParameter("selectedMaxZf", new BigDecimal(request.getSelectedMaxZf()));
                }
                if (StringUtils.isNotEmpty(request.getSelectedMinZf())){
                    query.setParameter("selectedMinZf", new BigDecimal(request.getSelectedMinZf()));
                }
                if (StringUtils.isNotEmpty(request.getCardType())) {
                    query.setParameter("cardType", request.getCardType());
                }
                if (StringUtils.isNotEmpty(request.getCardNumber())) {
                    query.setParameter("cardNumber", request.getCardNumber());
                } if (StringUtils.isNotEmpty(request.getSurveyWay())){
                    query.setParameter("surveyWay",request.getSurveyWay());
                } if (StringUtils.isNotEmpty(request.getRelation())){
                    query.setParameter("relation",request.getRelation());
                }if (StringUtils.isNotEmpty(request.getNotifyStatus())){
                    query.setParameter("notifyStatus",request.getNotifyStatus());
                } if (StringUtils.isNotEmpty(request.getDateStart())) {
                    query.setParameter("dateStart", request.getDateStart() + " 00:00:00");
                }
                if (StringUtils.isNotEmpty(request.getDateEnd())) {
                    query.setParameter("dateEnd", request.getDateEnd() + " 23:59:59");
                }
                List list = query.list();
                if (list.size() == 1) {
                    Object o = list.get(0);
                    BigInteger count = (BigInteger) o;
                    setResult(count);
                }
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    private StringBuffer getSatisfactionSurveySql(StringBuffer sql, final SurveyPlanListRequest request) {
        if (StringUtils.isNotEmpty(request.getSelectedZxksId())) {
            sql.append("and p.department_id=:selectedZxksId ");
        }
        if (StringUtils.isNotEmpty(request.getSelectedZxjg())) {
            sql.append("and p.organization_id=:selectedZxjg ");
        }
        if (StringUtils.isNotEmpty(request.getQuestionnaireId())) {
            sql.append("and r.questionnaire_id=:questionnaireId ");
        }
        if (StringUtils.isNotEmpty(request.getSurveyStatus())) {
            sql.append("and p.survey_status=:surveyStatus ");
        }
        if (StringUtils.isNotEmpty(request.getFormStatus())) {
            sql.append("and r.survey_status=:formStatus ");
        }
        if (StringUtils.isNotEmpty(request.getProcessingStatus())) {
            sql.append("and p.processing_status=:processingStatus ");
        }
//        if (StringUtils.isNotEmpty(request.getBeginDate())) {
//            sql.append("and p.plan_survey_time>='").append(request.getBeginDate()).append("' ");
//        }if (StringUtils.isNotEmpty(request.getSurveyWay())){
//            sql.append("and p.survey_way=:surveyWay ");
//        }
        if (CommonUtils.isNotNull(request.getDateStart())) {
            sql.append(" and plan_survey_time >=:dateStart ");
        }
        if (CommonUtils.isNotNull(request.getDateEnd())) {
            sql.append(" and plan_survey_time <=:dateEnd ");
        }
        if (StringUtils.isNotEmpty(request.getRelation())){
             sql.append("and p.relation=:relation ");
        }
        if(StringUtils.isNotEmpty(request.getNotifyStatus())){
            sql.append("and p.notify_status=:notifyStatus ");
        }
//        if (StringUtils.isNotEmpty(request.getEndDate())) {
//            Calendar calendar = Calendar.getInstance();
//            calendar.setTime(DateUtil.getDateTime("yyyy-MM-dd",request.getEndDate()));
//            calendar.add(Calendar.DAY_OF_MONTH,1);
//            sql.append("and p.plan_survey_time<'").append(DateUtil.formDate(calendar.getTime(), "yyyy-MM-dd")).append("' ");
//        }
        if(StringUtils.isNotEmpty(request.getSelectedMaxZf())){
            sql.append(" and p.survey_score <=:selectedMaxZf ");
        }
        if(StringUtils.isNotEmpty(request.getSelectedMinZf())){
            sql.append(" and p.survey_score >=:selectedMinZf ");
        }
        if (StringUtils.isNotEmpty(request.getCardType())) {
            sql.append("and p.card_type=:cardType ");
        }
        if (StringUtils.isNotEmpty(request.getCardNumber())) {
            sql.append("and p.card_number=:cardNumber ");
        }
        sql.append(" order by p.plan_survey_time desc ");
        return sql;
    }
    private StringBuffer getSatisfactionSurveySql1(StringBuffer sql, final SurveyPlanListRequest request) {
        if (StringUtils.isNotEmpty(request.getSelectedZxksId())) {
            sql.append("and p.department_id=:selectedZxksId ");
        }
        if (StringUtils.isNotEmpty(request.getSelectedZxjg())) {
            sql.append("and p.organization_id=:selectedZxjg ");
        }
        if (StringUtils.isNotEmpty(request.getQuestionnaireId())) {
            sql.append("and r.questionnaire_id=:questionnaireId ");
        }
        if (StringUtils.isNotEmpty(request.getSurveyStatus())) {
            sql.append("and p.survey_status=:surveyStatus ");
        }
        if (StringUtils.isNotEmpty(request.getFormStatus())) {
            sql.append("and r.survey_status=:formStatus ");
        }
        if (StringUtils.isNotEmpty(request.getProcessingStatus())) {
            sql.append("and p.processing_status=:processingStatus ");
        }
//        if (StringUtils.isNotEmpty(request.getBeginDate())) {
//            sql.append("and p.plan_survey_time>='").append(request.getBeginDate()).append("' ");
//        }if (StringUtils.isNotEmpty(request.getSurveyWay())){
//            sql.append("and p.survey_way=:surveyWay ");
//        }
        if (CommonUtils.isNotNull(request.getDateStart())) {
            sql.append(" and notify_time >=:dateStart ");
        }
        if (CommonUtils.isNotNull(request.getDateEnd())) {
            sql.append(" and notify_time <=:dateEnd ");
        }
        if (StringUtils.isNotEmpty(request.getRelation())){
            sql.append("and p.relation=:relation ");
        }
        if(StringUtils.isNotEmpty(request.getNotifyStatus())){
            sql.append("and p.notify_status=:notifyStatus ");
        }
//        if (StringUtils.isNotEmpty(request.getEndDate())) {
//            Calendar calendar = Calendar.getInstance();
//            calendar.setTime(DateUtil.getDateTime("yyyy-MM-dd",request.getEndDate()));
//            calendar.add(Calendar.DAY_OF_MONTH,1);
//            sql.append("and p.plan_survey_time<'").append(DateUtil.formDate(calendar.getTime(), "yyyy-MM-dd")).append("' ");
//        }
        if(StringUtils.isNotEmpty(request.getSelectedMaxZf())){
            sql.append(" and p.survey_score <=:selectedMaxZf ");
        }
        if(StringUtils.isNotEmpty(request.getSelectedMinZf())){
            sql.append(" and p.survey_score >=:selectedMinZf ");
        }
        if (StringUtils.isNotEmpty(request.getCardType())) {
            sql.append("and p.card_type=:cardType ");
        }
        if (StringUtils.isNotEmpty(request.getCardNumber())) {
            sql.append("and p.card_number=:cardNumber ");
        }
        sql.append(" order by p.plan_survey_time desc ");
        return sql;
    }

    /**
     * @param patientIds
     * @param idCards
     * @param patientMedicalCards
     * @param surveyStatus
     * @throws
     * @description: 获取满意度调查记录总数
     * @return: java.lang.Long
     * @author: tongtp
     * @date: 2020/9/29 17:43
     */
    public Long queryCount(final List<String> patientIds, final List<IdCard> idCards,
                           final List<PatientMedicalCard> patientMedicalCards,
                           final String surveyStatus) {
        HibernateStatelessResultAction<Long> action =
                new AbstractHibernateStatelessResultAction<Long>() {
                    @Override
                    public void execute(StatelessSession ss) throws Exception {
                        StringBuilder sqlBuilder = new StringBuilder();
                        sqlBuilder.append(" select count(*) as countValue " +
                                "from " +
                                "patient_satisfaction_survey where 1 = 1 ");
                        querySurveyBuildSql(sqlBuilder, surveyStatus, patientIds, idCards, patientMedicalCards);

                        Query query = ss.createSQLQuery(sqlBuilder.toString());
                        querySurveyHandleQuery(query, surveyStatus, patientIds, idCards, patientMedicalCards);
                        query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);

                        List<Map> dbList = query.list();

                        if (CommonUtils.isNotEmpty(dbList)) {
                            setResult(Long.valueOf(String.valueOf(dbList.get(0).get("countValue"))));
                        } else {
                            setResult(0L);
                        }
                    }
                };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    public Long queryCountForPatient(final List<String> patientIds, final List<IdCard> idCards,
                           final List<PatientMedicalCard> patientMedicalCards,
                           final String surveyStatus) {
        HibernateStatelessResultAction<Long> action =
                new AbstractHibernateStatelessResultAction<Long>() {
                    @Override
                    public void execute(StatelessSession ss) throws Exception {
                        StringBuilder sqlBuilder = new StringBuilder();
                        sqlBuilder.append(" select count(*) as countValue " +
                                "from " +
                                "patient_satisfaction_survey where route_detail_type = 3 ");
                        querySurveyBuildSql(sqlBuilder, surveyStatus, patientIds, idCards, patientMedicalCards);

                        Query query = ss.createSQLQuery(sqlBuilder.toString());
                        querySurveyHandleQuery(query, surveyStatus, patientIds, idCards, patientMedicalCards);
                        query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);

                        List<Map> dbList = query.list();

                        if (CommonUtils.isNotEmpty(dbList)) {
                            setResult(Long.valueOf(String.valueOf(dbList.get(0).get("countValue"))));
                        } else {
                            setResult(0L);
                        }
                    }
                };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    /**
     * @param sqlBuilder
     * @param surveyStatus
     * @param patientIds
     * @param idCards
     * @param patientMedicalCards
     * @throws
     * @description: 查询满意度列表
     * @return: void
     * @author: tongtp
     * @date: 2020/9/29 17:49
     */
    private void querySurveyBuildSql(StringBuilder sqlBuilder, String surveyStatus, List<String> patientIds,
                                     List<IdCard> idCards, List<PatientMedicalCard> patientMedicalCards) {
        if (CommonUtils.isNotNull(surveyStatus)) {
            sqlBuilder.append(" and survey_status = :surveyStatus ");
        }
        boolean orFlag = false;
        if (!CollectionUtils.isEmpty(patientIds)) {
            if (orFlag) {
                sqlBuilder.append(" or (");
            } else {
                sqlBuilder.append(" and ( ");
                orFlag = true;
            }
            sqlBuilder.append(" patient_id in (:patientIds) ");
        }
        if (!CollectionUtils.isEmpty(idCards)) {
            if (orFlag) {
                sqlBuilder.append(" or ( ");
            } else {
                sqlBuilder.append(" and (( ");
                orFlag = true;
            }
            for (int i = 0; i < idCards.size(); i++) {
                if (i != 0) {
                    sqlBuilder.append(" or ");
                }
                sqlBuilder.append(" (card_type = :cardType" + i);
                sqlBuilder.append(" and card_number = :cardNumber" + i + ")");
            }
            sqlBuilder.append(" ) ");
        }
        if (!CollectionUtils.isEmpty(patientMedicalCards)) {
            if (orFlag) {
                sqlBuilder.append(" or (");
            } else {
                sqlBuilder.append(" and (( ");
                orFlag = true;
            }
            for (int i = 0; i < patientMedicalCards.size(); i++) {
                if (i != 0) {
                    sqlBuilder.append(" or ");
                }
                sqlBuilder.append(" (patient_medical_card_type = :patientMedicalCardType" + i);
                sqlBuilder.append(" and patient_medical_card_number = :patientMedicalCardNumber" + i + ")");
            }
            sqlBuilder.append(" ) ");
        }
        sqlBuilder.append(" ) ");
        sqlBuilder.append("  order by plan_survey_time desc ");
    }

    /**
     * @param patientIds
     * @param idCards
     * @param patientMedicalCards
     * @param surveyStatus
     * @param pageNo
     * @param pageSize
     * @throws
     * @description: 获取满意度调查记录列表
     * @return: java.util.List<satisfaction.entity.PatientSatisfactionSurvey>
     * @author: tongtp
     * @date: 2020/9/29 17:47
     */
    public List<PatientSatisfactionSurvey> queryList(final List<String> patientIds, final List<IdCard> idCards,
                                                     final List<PatientMedicalCard> patientMedicalCards,
                                                     final String surveyStatus, final Integer pageNo,
                                                     final Integer pageSize) {
        HibernateStatelessResultAction<List<PatientSatisfactionSurvey>> action =
                new AbstractHibernateStatelessResultAction<List<PatientSatisfactionSurvey>>() {
                    @Override
                    public void execute(StatelessSession ss) throws Exception {
                        StringBuilder sqlBuilder = new StringBuilder();
                        sqlBuilder.append(" select patient_survey_id as patientSurveyId,business_type as " +
                                "businessType,business_id as businessId,patient_name as patientName,patient_sex as " +
                                "patientSex,patient_age as patientAge,telephone as telephone,card_type as cardType," +
                                "card_number as cardNumber,address as " +
                                "address,patient_medical_card_type as patientMedicalCardType," +
                                "patient_medical_card_number as " +
                                "patientMedicalCardNumber,companion_name as contactPersonName," +
                                "companion_phone as companionPhone,plan_survey_time as notifyTime,survey_status " +
                                "as surveyStatus,processing_status as processingStatus," +
                                "is_notify as isNotify,notify_status as notifyStatus, " +
                                "department_id as departmentId," +
                                "department_name as " +
                                "departmentName,survey_time as surveyTime " +
                                "from " +
                                "patient_satisfaction_survey where 1 = 1 ");
                        querySurveyBuildSql(sqlBuilder, surveyStatus, patientIds, idCards, patientMedicalCards);

                        Query query = ss.createSQLQuery(sqlBuilder.toString());
                        querySurveyHandleQuery(query, surveyStatus, patientIds, idCards, patientMedicalCards);

                        query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
                        //分页
                        int first = pageNo > 0 ? (pageNo - 1) * pageSize : 0;
                        query.setFirstResult(first);
                        query.setMaxResults(pageSize);
                        List<Map> list = query.list();
                        List<PatientSatisfactionSurvey> voList = new ArrayList<>();
                        if (CommonUtils.isNotEmpty(list)) {
                            for (Map map : list) {
                                voList.add(ConversionUtils.convert(map, PatientSatisfactionSurvey.class));
                            }
                        } else {
                            voList = new ArrayList<>();
                        }
                        setResult(voList);
                    }
                };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    public List<PatientSatisfactionSurvey> queryListForPatient(final List<String> patientIds, final List<IdCard> idCards,
                                                     final List<PatientMedicalCard> patientMedicalCards,
                                                     final String surveyStatus, final Integer pageNo,
                                                     final Integer pageSize) {
        HibernateStatelessResultAction<List<PatientSatisfactionSurvey>> action =
                new AbstractHibernateStatelessResultAction<List<PatientSatisfactionSurvey>>() {
                    @Override
                    public void execute(StatelessSession ss) throws Exception {
                        StringBuilder sqlBuilder = new StringBuilder();
                        sqlBuilder.append(" select patient_survey_id as patientSurveyId,business_type as " +
                                "businessType,business_id as businessId,patient_name as patientName,patient_sex as " +
                                "patientSex,patient_age as patientAge,telephone as telephone,card_type as cardType," +
                                "card_number as cardNumber,address as " +
                                "address,patient_medical_card_type as patientMedicalCardType," +
                                "patient_medical_card_number as " +
                                "patientMedicalCardNumber,companion_name as contactPersonName," +
                                "companion_phone as companionPhone,plan_survey_time as notifyTime,survey_status " +
                                "as surveyStatus,processing_status as processingStatus," +
                                "is_notify as isNotify,notify_status as notifyStatus, " +
                                "department_id as departmentId," +
                                "department_name as " +
                                "departmentName,survey_time as surveyTime " +
                                "from " +
                                "patient_satisfaction_survey where route_detail_type = 3 ");
                        querySurveyBuildSql(sqlBuilder, surveyStatus, patientIds, idCards, patientMedicalCards);

                        Query query = ss.createSQLQuery(sqlBuilder.toString());
                        querySurveyHandleQuery(query, surveyStatus, patientIds, idCards, patientMedicalCards);

                        query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
                        //分页
                        int first = pageNo > 0 ? (pageNo - 1) * pageSize : 0;
                        query.setFirstResult(first);
                        query.setMaxResults(pageSize);
                        List<Map> list = query.list();
                        List<PatientSatisfactionSurvey> voList = new ArrayList<>();
                        if (CommonUtils.isNotEmpty(list)) {
                            for (Map map : list) {
                                voList.add(ConversionUtils.convert(map, PatientSatisfactionSurvey.class));
                            }
                        } else {
                            voList = new ArrayList<>();
                        }
                        setResult(voList);
                    }
                };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    private void querySurveyHandleQuery(Query query, String surveyStatus, List<String> patientIds,
                                        List<IdCard> idCards, List<PatientMedicalCard> patientMedicalCards) {
        if (CommonUtils.isNotNull(surveyStatus)) {
            query.setParameter("surveyStatus", surveyStatus);
        }
        if (!CollectionUtils.isEmpty(patientIds)) {
            query.setParameterList("patientIds", patientIds);
        }
        if (!CollectionUtils.isEmpty(idCards)) {
            for (int i = 0; i < idCards.size(); i++) {
                IdCard idCard = idCards.get(i);
                query.setParameter("cardType" + i, idCard.getCardType());
                query.setParameter("cardNumber" + i, idCard.getCardNo());
            }
        }
        if (!CollectionUtils.isEmpty(patientMedicalCards)) {
            for (int i = 0; i < patientMedicalCards.size(); i++) {
                PatientMedicalCard patientMedicalCard = patientMedicalCards.get(i);
                query.setParameter("patientMedicalCardType" + i,
                        patientMedicalCard.getPatientMedicalCardType());
                query.setParameter("patientMedicalCardNumber" + i,
                        patientMedicalCard.getPatientMedicalCardNumber());
            }
        }
    }

    /**
     * @param request
     * @throws
     * @description: 获取满意度调查计划列表总数
     * @return: java.util.List<specialist.entity.FollowUpRecords>
     * @author: tongtp
     * @date: 2020/7/23 9:51
     */
    public Long queryCount(final QueryListByDoctorRequest request,
                           final List<Long> ruleIds) {
        HibernateStatelessResultAction<Long> action =
                new AbstractHibernateStatelessResultAction<Long>() {
                    @Override
                    public void execute(StatelessSession ss) throws Exception {
                        StringBuilder sqlBuilder = new StringBuilder();
                        sqlBuilder.append(" select count(*) as countValue " +
                                "from " +
                                "patient_satisfaction_survey where ");
                        sqlBuilder.append(" survey_rule_id in (:ruleIds) ");
                        if (CommonUtils.isNotNull(request.getBeginDate())) {
                            sqlBuilder.append(" and plan_survey_time >= :beginDate ");
                        }
                        if (CommonUtils.isNotNull(request.getEndDate())) {
                            sqlBuilder.append(" and plan_survey_time <= :endDate ");
                        }
                        if (CommonUtils.isNotNull(request.getProcessingStatus())) {
                            sqlBuilder.append(" and processing_status = :processingStatus ");
                        }
                        if (CommonUtils.isNotNull(request.getSurveyStatus())) {
                            sqlBuilder.append(" and survey_status = :surveyStatus ");
                        }
                        if (CommonUtils.isNotNull(request.getPatientName())) {

                            if(isNumber(request.getPatientName())){
                                sqlBuilder.append(" and patient_medical_card_number like :patientName ");
                            }else{
                                sqlBuilder.append(" and patient_name like :patientName ");
                            }

                        }
                        if (CommonUtils.isNotNull(request.getBusinessType())) {
                            sqlBuilder.append(" and business_type = :businessType ");
                        }
                        sqlBuilder.append("  order by plan_survey_time desc ");

                        Query query = ss.createSQLQuery(sqlBuilder.toString());
                        query.setParameterList("ruleIds", ruleIds);
                        if (CommonUtils.isNotNull(request.getBeginDate())) {
                            query.setParameter("beginDate", request.getBeginDate());
                        }
                        if (CommonUtils.isNotNull(request.getEndDate())) {
                            query.setParameter("endDate", request.getEndDate());
                        }
                        if (CommonUtils.isNotNull(request.getProcessingStatus())) {
                            query.setParameter("processingStatus", request.getProcessingStatus());
                        }
                        if (CommonUtils.isNotNull(request.getSurveyStatus())) {
                            query.setParameter("surveyStatus", request.getSurveyStatus());
                        }
                        if (CommonUtils.isNotNull(request.getPatientName())) {
                            query.setParameter("patientName", "%"+request.getPatientName()+"%");
                        }
                        if (CommonUtils.isNotNull(request.getBusinessType())) {
                            query.setParameter("businessType", request.getBusinessType());
                        }
                        query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);

                        List<Map> dbList = query.list();

                        if (CommonUtils.isNotEmpty(dbList)) {
                            setResult(Long.valueOf(String.valueOf(dbList.get(0).get("countValue"))));
                        } else {
                            setResult(0L);
                        }
                    }
                };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }
    private boolean isNumber(String number) {
        if (org.springframework.util.StringUtils.isEmpty(number)) {
            return false;
        }
        Pattern p = Pattern.compile("[0-9]+");
        Matcher m = p.matcher(number);
        return m.find();
    }

    /**
     * @param request
     * @throws
     * @description: 获取满意度调查计划列表
     * @return: java.util.List<specialist.entity.FollowUpRecords>
     * @author: tongtp
     * @date: 2020/7/23 9:51
     */
    public List<PatientSatisfactionSurvey> queryList(final QueryListByDoctorRequest request,
                                                     final List<Long> ruleIds) {
        HibernateStatelessResultAction<List<PatientSatisfactionSurvey>> action =
                new AbstractHibernateStatelessResultAction<List<PatientSatisfactionSurvey>>() {
                    @Override
                    public void execute(StatelessSession ss) throws Exception {
                        StringBuilder sqlBuilder = new StringBuilder();
                        sqlBuilder.append("select patient_survey_id as patientSurveyId,business_type as " +
                                "businessType,business_id as businessId,patient_name as patientName,patient_sex as " +
                                "patientSex,patient_age as patientAge,telephone as telephone,card_type as cardType," +
                                "card_number as cardNumber,address as " +
                                "address,patient_medical_card_type as patientMedicalCardType," +
                                "patient_medical_card_number as " +
                                "patientMedicalCardNumber,companion_name as contactPersonName," +
                                "companion_phone as companionPhone,plan_survey_time as notifyTime,survey_status " +
                                "as surveyStatus,processing_status as processingStatus," +
                                "is_notify as isNotify,notify_status as notifyStatus, " +
                                "department_id as departmentId," +
                                "department_name as " +
                                "departmentName, route_detail_type AS routeDetailType" +
                                " from " +
                                "patient_satisfaction_survey where ");
                        sqlBuilder.append(" survey_rule_id in (:ruleIds) ");
                        if (CommonUtils.isNotNull(request.getBeginDate())) {
                            sqlBuilder.append(" and plan_survey_time >= :beginDate ");
                        }
                        if (CommonUtils.isNotNull(request.getEndDate())) {
                            sqlBuilder.append(" and plan_survey_time <= :endDate ");
                        }
                        if (CommonUtils.isNotNull(request.getProcessingStatus())) {
                            sqlBuilder.append(" and processing_status = :processingStatus ");
                        }
                        if (CommonUtils.isNotNull(request.getSurveyStatus())) {
                            sqlBuilder.append(" and survey_status = :surveyStatus ");
                        }
                        if (CommonUtils.isNotNull(request.getBusinessType())) {
                            sqlBuilder.append(" and business_type = :businessType ");
                        }
                        if (CommonUtils.isNotNull(request.getPatientName())) {
                            if(isNumber(request.getPatientName())){
                                sqlBuilder.append(" and patient_medical_card_number like :patientName ");
                            }else{
                                sqlBuilder.append(" and patient_name like :patientName ");
                            }
                        }
                        sqlBuilder.append("  order by plan_survey_time desc ");

                        Query query = ss.createSQLQuery(sqlBuilder.toString());
                        query.setParameterList("ruleIds", ruleIds);
                        if (CommonUtils.isNotNull(request.getBeginDate())) {
                            query.setParameter("beginDate", request.getBeginDate());
                        }
                        if (CommonUtils.isNotNull(request.getEndDate())) {
                            query.setParameter("endDate", request.getEndDate());
                        }
                        if (CommonUtils.isNotNull(request.getProcessingStatus())) {
                            query.setParameter("processingStatus", request.getProcessingStatus());
                        }
                        if (CommonUtils.isNotNull(request.getSurveyStatus())) {
                            query.setParameter("surveyStatus", request.getSurveyStatus());
                        }
                        if (CommonUtils.isNotNull(request.getPatientName())) {
                            query.setParameter("patientName", "%"+request.getPatientName()+"%");
                        }
                        if (CommonUtils.isNotNull(request.getBusinessType())) {
                            query.setParameter("businessType", request.getBusinessType());
                        }

                        query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
                        //分页
                        int first = request.getPageNo() > 0 ? (request.getPageNo() - 1) * request.getPageSize() : 0;
                        query.setFirstResult(first);
                        query.setMaxResults(request.getPageSize());
                        List<Map> list = query.list();
                        List<PatientSatisfactionSurvey> voList = new ArrayList<>();
                        if (CommonUtils.isNotEmpty(list)) {
                            for (Map map : list) {
                                voList.add(ConversionUtils.convert(map, PatientSatisfactionSurvey.class));
                            }
                        } else {
                            voList = new ArrayList<>();
                        }
                        setResult(voList);
                    }
                };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    public List<SurveyDepartmentListResponse> querySurveyDepartmentList(final SurveyDepartmentListRequest request) {
        HibernateStatelessResultAction<List<SurveyDepartmentListResponse>> action = new AbstractHibernateStatelessResultAction<List<SurveyDepartmentListResponse>>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer sql = new StringBuffer();
                sql.append("select s.patient_survey_id as patientSurveyId,s.survey_answer_id as surveyAnswerId,s.questionnaire_id as questionnaireId,");
                sql.append("s.questionnaire_name as questionnaireName,s.title_id as titleId,s.title_name as titleName,s.answer_id as answerId,");
                sql.append("s.answer_name as answerName,d.medical_team_id as medicalTeamId,d.medical_team_name as medicalTeamName ");
                sql.append("from patient_satisfaction_survey_answer s ");
                sql.append("left join doc_team_survey_questionnaire d on d.questionnaire_id=s.questionnaire_id ");
                sql.append("where patient_survey_id =:patientSurveyId and s.title_id=d.title_id ");
                if (CommonUtils.isNotEmpty(request.getQuestionnaireId())){
                    sql.append("and s.questionnaire_id=:questionnaireId ");
                }
                Query query = ss.createSQLQuery(sql.toString());
                query.setParameter("patientSurveyId", request.getPatientSurveyId());
                if (CommonUtils.isNotEmpty(request.getQuestionnaireId())){
                    query.setParameter("questionnaireId",request.getQuestionnaireId());
                }
                query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                List<SurveyDepartmentListResponse> departmentList = new ArrayList<SurveyDepartmentListResponse>();
                List<Map> list = query.list();
                if (list.size() > 0) {
                    for (Map map : list) {
                        departmentList.add(ConversionUtils.convert(map, SurveyDepartmentListResponse.class));
                    }
                    setResult(departmentList);
                } else {
                    setResult(null);
                }
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    public List<SurveyDealWithPlanListResponse.DealWithPlanBean> querySurveyDealWithPlanList(final SurveyDealWithPlanListRequest request) {
        HibernateStatelessResultAction<List<SurveyDealWithPlanListResponse.DealWithPlanBean>> action = new AbstractHibernateStatelessResultAction<List<SurveyDealWithPlanListResponse.DealWithPlanBean>>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer sql = new StringBuffer();
                sql.append("select s.questionnaire_id as questionnaireId, s.questionnaire_name as questionnaireName, p.patient_name as patientName,p.patient_age as patientAge,");
                sql.append("p.patient_sex as patientSex,p.department_id as departmentId,p.department_name as departmentName, ");
                sql.append("p.telephone,notify_time as notifyTime,p.survey_status as surveyStatus,s.survey_status AS formStatus,p.patient_survey_id as patientSurveyId,");
                sql.append("p.business_type as recordType,p.business_id as businessId,p.survey_time as surveyTime,p.companion_phone as contactTelephone,");
                sql.append("p.companion_name as contactPersonName, ");
                sql.append("p.doctor_name as  doctorName ");
                sql.append("from patient_satisfaction_survey p ");
                sql.append(" inner join satisfaction_requisition_record s on p.patient_survey_id=s.record_id ");
                sql.append("where 1=1 and p.processing_status in ('1','2') ");
                if (request.getPatientSurveyIds() != null && request.getPatientSurveyIds().size() > 0) {
                    sql.append("and p.patient_survey_id in (:patientSurveyIds) ");
                }
                if (StringUtils.isNotEmpty(request.getQuestionnaireName())) {
                    sql.append("and s.questionnaire_name like '%").append(request.getQuestionnaireName()).append("%' ");
                }

                if (StringUtils.isNotEmpty(request.getQuestionnaireId())) {
                    sql.append("and s.questionnaire_id=:questionnaireId ");
                }

                if (StringUtils.isNotEmpty(request.getPatientName())) {
                    sql.append("and p.patient_name like '%").append(request.getPatientName()).append("%' ");
                }
                if (StringUtils.isNotEmpty(request.getTelephone())) {
                    sql.append("and p.telephone=:telephone ");
                }
                if (StringUtils.isNotEmpty(request.getBeginDate())) {
                    sql.append("and p.survey_time>=:beginDate ");
                }
                if (StringUtils.isNotEmpty(request.getEndDate())) {
                    sql.append("and p.survey_time<=:endDate ");
                }
                sql.append(" order by p.survey_time desc");
                Query query = ss.createSQLQuery(sql.toString());

                if (request.getPatientSurveyIds() != null && request.getPatientSurveyIds().size() > 0) {
                    query.setParameterList("patientSurveyIds", request.getPatientSurveyIds());
                }

                if (StringUtils.isNotEmpty(request.getQuestionnaireId())) {
                    query.setParameter("questionnaireId", request.getQuestionnaireId());
                }

                if (StringUtils.isNotEmpty(request.getTelephone())) {
                    query.setParameter("telephone", request.getTelephone());
                }
                if (StringUtils.isNotEmpty(request.getBeginDate())) {
                    query.setParameter("beginDate", request.getBeginDate() + " 00:00:00");
                }
                if (StringUtils.isNotEmpty(request.getEndDate())) {
                    query.setParameter("endDate", request.getEndDate() + " 23:59:59");
                }
                query.setFirstResult((request.getPageNo() - 1) * request.getPageSize());
                query.setMaxResults(request.getPageSize());
                query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                List<SurveyDealWithPlanListResponse.DealWithPlanBean> followUpPlanList = new ArrayList<SurveyDealWithPlanListResponse.DealWithPlanBean>();
                List<Map> list = query.list();
                if (list.size() > 0) {
                    for (Map map : list) {
                        followUpPlanList.add(ConversionUtils.convert(map, SurveyDealWithPlanListResponse.DealWithPlanBean.class));
                    }
                    setResult(followUpPlanList);
                } else {
                    setResult(null);
                }
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    public BigInteger getSurveyDealWithPlanCount(final SurveyDealWithPlanListRequest request) {
        HibernateStatelessResultAction<BigInteger> action = new AbstractHibernateStatelessResultAction<BigInteger>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer sql = new StringBuffer();
                sql.append("select count(1) ");
                sql.append("from patient_satisfaction_survey p ");
                sql.append(" inner join satisfaction_requisition_record s on p.patient_survey_id=s.record_id ");
                sql.append("where 1=1 and p.processing_status in ('1','2') ");
                if (request.getPatientSurveyIds() != null && request.getPatientSurveyIds().size() > 0) {
                    sql.append("and p.patient_survey_id in (:patientSurveyIds) ");
                }
                if (StringUtils.isNotEmpty(request.getQuestionnaireName())) {
                    sql.append("and s.questionnaire_name like '%").append(request.getQuestionnaireName()).append("%' ");
                }

                if (StringUtils.isNotEmpty(request.getQuestionnaireId())) {
                    sql.append("and s.questionnaire_id=:questionnaireId ");
                }

                if (StringUtils.isNotEmpty(request.getPatientName())) {
                    sql.append("and p.patient_name like '%").append(request.getPatientName()).append("%' ");
                }
                if (StringUtils.isNotEmpty(request.getTelephone())) {
                    sql.append("and p.telephone=:telephone ");
                }
                if (StringUtils.isNotEmpty(request.getBeginDate())) {
                    sql.append("and p.survey_time>=:beginDate ");
                }
                if (StringUtils.isNotEmpty(request.getEndDate())) {
                    sql.append("and p.survey_time<=:endDate");
                }

                Query query = ss.createSQLQuery(sql.toString());

                if (request.getPatientSurveyIds() != null && request.getPatientSurveyIds().size() > 0) {
                    query.setParameterList("patientSurveyIds", request.getPatientSurveyIds());
                }
                if (StringUtils.isNotEmpty(request.getQuestionnaireId())) {
                    query.setParameter("questionnaireId", request.getQuestionnaireId());
                }
                if (StringUtils.isNotEmpty(request.getTelephone())) {
                    query.setParameter("telephone", request.getTelephone());
                }
                if (StringUtils.isNotEmpty(request.getBeginDate())) {
                    query.setParameter("beginDate", request.getBeginDate() + " 00:00:00");
                }
                if (StringUtils.isNotEmpty(request.getEndDate())) {
                    query.setParameter("endDate", request.getEndDate() + " 23:59:59");
                }
                List list = query.list();
                if (list.size() == 1) {
                    Object o = list.get(0);
                    BigInteger count = (BigInteger) o;
                    setResult(count);
                }
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    public List<SurveyDealWithPlanDetailResponse.TitleBean> getSurveyDealWithPlanDetail(final SurveyDealWithPlanDetailRequest request) {
        HibernateStatelessResultAction<List<SurveyDealWithPlanDetailResponse.TitleBean>> action = new AbstractHibernateStatelessResultAction<List<SurveyDealWithPlanDetailResponse.TitleBean>>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer sql = new StringBuffer();
                sql.append("select p.title_id as titleId,REPLACE(REPLACE(p.title_name,'<p>',''),'</p>','') AS titleName,p.questionnaire_id as questionnaireId,");
                sql.append("p.questionnaire_name as questionnaireName,p.processing_status as processingStatus,");
                sql.append("p.processing_time as processingTime,p.processing_doctor_name as processingDoctorName,");
                sql.append("p.processing_doctor_id as processingDoctorId,p.answer_id as answerId,p.answer_name as answerName,");
                sql.append("p.notify_flag as isNotify,ps.survey_way as surveyWay,ps.patient_age as patientAge,");
                sql.append("ps.patient_id as patientId,ps.patient_name as patientName,ps.patient_sex as patientSex,");
                sql.append("ps.survey_time as surveyTime,ps.telephone,ps.hospital_number as hospitalNumber,");
                sql.append("p.survey_answer_id as surveyAnswerId,t.patient_survey_id as patientSurveyId, ");
                sql.append("p.processing_opinions as processingOpinions ");
                sql.append("from team_survey_record t ");
                sql.append("left join patient_satisfaction_survey_answer p on t.patient_survey_id=p.patient_survey_id ");
                sql.append("left join patient_satisfaction_survey ps on ps.patient_survey_id=t.patient_survey_id ");
                sql.append("where 1=1 and t.patient_survey_id=:patientSurveyId and t.questionnaire_id=p.questionnaire_id and t.title_id=p.title_id ");
                if (request.getMedicalTeamIds() != null && request.getMedicalTeamIds().size() > 0) {
                    sql.append("and t.medical_team_id in (:medicalTeamIds) ");
                }
                if (StringUtils.isNotEmpty(request.getNotifyFlag())) {
                    sql.append("and p.notify_flag=:notifyFlag ");
                }
                if (StringUtils.isNotEmpty(request.getProcessingStatus())) {
                    sql.append("and p.processing_status=:processingStatus ");
                }
                Query query = ss.createSQLQuery(sql.toString());
                if (request.getMedicalTeamIds() != null && request.getMedicalTeamIds().size() > 0) {
                    query.setParameterList("medicalTeamIds", request.getMedicalTeamIds());
                }
                if (StringUtils.isNotEmpty(request.getNotifyFlag())) {
                    query.setParameter("notifyFlag", request.getNotifyFlag());
                }
                if (StringUtils.isNotEmpty(request.getProcessingStatus())) {
                    query.setParameter("processingStatus", request.getProcessingStatus());
                }
                query.setParameter("patientSurveyId", request.getPatientSurveyId());
                query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                List<SurveyDealWithPlanDetailResponse.TitleBean> dealWithPlanDetailList = new ArrayList<SurveyDealWithPlanDetailResponse.TitleBean>();
                List<Map> list = query.list();
                if (list.size() > 0) {
                    for (Map map : list) {
                        dealWithPlanDetailList.add(ConversionUtils.convert(map, SurveyDealWithPlanDetailResponse.TitleBean.class));
                    }
                    setResult(dealWithPlanDetailList);
                } else {
                    setResult(null);
                }
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }
    public List<SurveyDealWithPlanDetailResponse.TitleBean> getSurveyDealWithPlanDetailByDoctor(final SurveyDealWithPlanDetailRequest request) {
        HibernateStatelessResultAction<List<SurveyDealWithPlanDetailResponse.TitleBean>> action = new AbstractHibernateStatelessResultAction<List<SurveyDealWithPlanDetailResponse.TitleBean>>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer sql = new StringBuffer();
                sql.append("select p.title_id as titleId,REPLACE(REPLACE(p.title_name,'<p>',''),'</p>','') AS titleName,p.questionnaire_id as questionnaireId,");
                sql.append("p.questionnaire_name as questionnaireName,p.processing_status as processingStatus,");
                sql.append("p.processing_time as processingTime,p.processing_doctor_name as processingDoctorName,");
                sql.append("p.processing_doctor_id as processingDoctorId,p.answer_id as answerId,p.answer_name as answerName,");
                sql.append("p.notify_flag as isNotify,ps.survey_way as surveyWay,ps.patient_age as patientAge,");
                sql.append("ps.patient_id as patientId,ps.patient_name as patientName,ps.patient_sex as patientSex,");
                sql.append("ps.survey_time as surveyTime,ps.telephone,ps.hospital_number as hospitalNumber,");
                sql.append("p.survey_answer_id as surveyAnswerId,t.patient_survey_id as patientSurveyId, ");
                sql.append("p.processing_opinions as processingOpinions ");
                sql.append("from patient_satisfaction_survey_answer p ");
                sql.append("left join team_survey_record t on p.title_id = t.title_id and p.patient_survey_id=t.patient_survey_id ");
                sql.append("left join patient_satisfaction_survey ps on ps.patient_survey_id=t.patient_survey_id ");
                sql.append("where 1=1 and p.patient_survey_id=:patientSurveyId ");
                if (request.getMedicalTeamIds() != null && request.getMedicalTeamIds().size() > 0) {
                    sql.append("and t.medical_team_id in (:medicalTeamIds) ");
                }
                if (StringUtils.isNotEmpty(request.getNotifyFlag())) {
                    sql.append("and p.notify_flag=:notifyFlag ");
                }
                if (StringUtils.isNotEmpty(request.getProcessingStatus())) {
                    sql.append("and p.processing_status=:processingStatus ");
                }
                Query query = ss.createSQLQuery(sql.toString());
                if (request.getMedicalTeamIds() != null && request.getMedicalTeamIds().size() > 0) {
                    query.setParameterList("medicalTeamIds", request.getMedicalTeamIds());
                }
                if (StringUtils.isNotEmpty(request.getNotifyFlag())) {
                    query.setParameter("notifyFlag", request.getNotifyFlag());
                }
                if (StringUtils.isNotEmpty(request.getProcessingStatus())) {
                    query.setParameter("processingStatus", request.getProcessingStatus());
                }
                query.setParameter("patientSurveyId", request.getPatientSurveyId());
                query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                List<SurveyDealWithPlanDetailResponse.TitleBean> dealWithPlanDetailList = new ArrayList<SurveyDealWithPlanDetailResponse.TitleBean>();
                List<Map> list = query.list();
                if (list.size() > 0) {
                    for (Map map : list) {
                        dealWithPlanDetailList.add(ConversionUtils.convert(map, SurveyDealWithPlanDetailResponse.TitleBean.class));
                    }
                    setResult(dealWithPlanDetailList);
                } else {
                    setResult(null);
                }
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    @RpcService
    public List<QuerySurveyRuleQuestionListResponse> querySurveyRuleByDoctorId(final QuerySurveyRuleListRequest req) {
        HibernateStatelessResultAction<List<QuerySurveyRuleQuestionListResponse>> action =
                new AbstractHibernateStatelessResultAction<List<QuerySurveyRuleQuestionListResponse>>() {
                    @Override
                    public void execute(StatelessSession ss) {

                        StringBuffer stringBuffer = new StringBuffer();

                        stringBuffer.append("select a.organization_id organizationId,a.organization_name " +
                                "organizationName,a.route_id surveyRuleId,a.route_name surveyRuleName,d" +
                                ".form_record_id questionnaireId, d.form_name questionnaireName, d.item_type AS itemType," +
                                " education_id AS educationId, education_name AS educationName from " +
                                " follow_up_route_detail s, doc_team_fus_authority a," +
                                " follow_up_route ssr,follow_up_route_detail_form d where " +
                                " s.route_id = a.route_id and s.route_id = ssr.route_id " +
                                " and s.route_detail_id = d.route_detail_id and " +
                                " (d.item_type = 1 or d.item_type = 3 )");
                        if (CommonUtils.isNotEmpty(req.getTeamIdList())) {
                            stringBuffer.append(" and a.medical_team_id in(:teamIdList) ");
                        }
                        if (CommonUtils.isNotEmpty(req.getSurveyRuleId())) {
                            stringBuffer.append(" and s.route_id =:survey_rule_id ");
                        }
                        if (CommonUtils.isNotEmpty(req.getVisitType())) {
                            stringBuffer.append(" and ssr.visit_type =:visit_type ");
                        }

                        Query sqlQuery = ss.createSQLQuery(stringBuffer.toString());

                        if (CommonUtils.isNotEmpty(req.getTeamIdList())) {
                            sqlQuery.setParameterList("teamIdList", req.getTeamIdList());
                        }
                        if (CommonUtils.isNotEmpty(req.getSurveyRuleId())) {
                            sqlQuery.setParameter("survey_rule_id", req.getSurveyRuleId());
                        }
                        if (CommonUtils.isNotEmpty(req.getVisitType())) {
                            sqlQuery.setParameter("visit_type", req.getVisitType());
                        }

                        sqlQuery.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                        List<Map> dbList = sqlQuery.list();
                        setResult(mapToList(dbList));
                    }
                };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    private List<QuerySurveyRuleQuestionListResponse> mapToList(List<Map> maps) {
        List<QuerySurveyRuleQuestionListResponse> docTeamListRespList = new ArrayList<>();
        for (Map map : maps) {
            docTeamListRespList.add(ConversionUtils.convert(map, QuerySurveyRuleQuestionListResponse.class));
        }
        return docTeamListRespList;
    }

    public List<QueryQuestionRecordsResp> queryQuestionRecords(final List<String> userInfoIds) {
        HibernateStatelessResultAction<List<QueryQuestionRecordsResp>> action = new AbstractHibernateStatelessResultAction<List<QueryQuestionRecordsResp>>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuilder sqlBuilder = new StringBuilder();
                sqlBuilder.append(" select organization_name organizationName,survey_source_type surveySourceType," +
                        " (case operation_doctor when null then operation_doctor else (select i.name from base_orgdoctors b,doct_info i where b.doctorId=i.doctorId and b.localDoctorId=operation_doctor) end) operationDoctor," +
                        " user_info_id userInfoId " +
                        "  from patient_satisfaction_survey where 1=1 ");

                if (CommonUtils.isNotEmpty(userInfoIds)){
                    sqlBuilder.append(" and user_info_id in (:userInfoIds)");

                }

                Query query = ss.createSQLQuery(sqlBuilder.toString());

                if (CommonUtils.isNotEmpty(userInfoIds)){
                    query.setParameterList("userInfoIds",userInfoIds);
                }
                query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);

                List<Map> list = query.list();
                List<QueryQuestionRecordsResp> voList = new ArrayList<>();
                if (CommonUtils.isNotEmpty(list)) {
                    for (Map map : list) {
                        voList.add(ConversionUtils.convert(map, QueryQuestionRecordsResp.class));
                    }
                } else {
                    voList = new ArrayList<>();
                }
                setResult(voList);
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }
    public QueryDoctorTotalResponse queryDoctorWorkListTotal(QuerySurveyDeptListRequest request) {
        HibernateStatelessResultAction<QueryDoctorTotalResponse> action = new AbstractHibernateStatelessResultAction<QueryDoctorTotalResponse>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer stringBuffer= new StringBuffer();
                stringBuffer.append(" SELECT\n" +
                        "\tsum(dd.push) AS totalPush,\n" +
                        "\tsum(dd.back) AS totalBack,\n" +
                        "\tsum(dd.myl) / sum(dd.back) AS totalSum\n" +
                        "FROM\n" +
                        "\t(\n" +
                        "\t\tSELECT\n" +
                        "\t\t\tt.questionnaire_id,\n" +
                        "\t\t\tt.doctor_id AS doctorId,\n" +
                        "\t\t\tt.doctor_name AS doctorName,\n" +
                        "\t\t\tt.survey_status AS push,\n" +
                        "\t\t\tf.survey_status1 AS back,\n" +
                        "\t\t\t#t.survey_status / f.survey_status1 AS c,\n" +
                        "\t\t\th.abc AS myl,\n" +
                        "\t\t\tIFNULL(h.abc, 0) / f.survey_status1 AS c\n" +
                        "\t\tFROM\n" +
                        "\t\t\t(\n" +
                        "\t\t\t\tSELECT\n" +
                        "\t\t\t\t\tquestionnaire_id,\n" +
                        "\t\t\t\t\tdoctor_id,\n" +
                        "\t\t\t\t\tdoctor_name,\n" +
                        "\t\t\t\t\tcount(survey_status) AS survey_status\n" +
                        "\t\t\t\tFROM\n" +
                        "\t\t\t\t\tpatient_satisfaction_survey\n" +
                        "\t\t\t\tWHERE\n" +
                        "\t\t\t\t\tdoctor_id IS NOT NULL ");


                if (StringUtils.isNotEmpty(request.getBeginDate())) {
                  stringBuffer.append("and notify_time>=:beginDate  ");
                }
                if (StringUtils.isNotEmpty(request.getEndDate())) {
                    stringBuffer.append("and notify_time<=:endDate  ");
                }
                if (StringUtils.isNotEmpty(request.getQuestionnaireId())){
                    stringBuffer.append("and questionnaire_id=:questionnaire_id ");
                }
                stringBuffer.append(" GROUP BY\n" +
                        "\t\t\t\t\tdoctor_id\n" +
                        "\t\t\t) t\n" +
                        "\t\tLEFT JOIN (\n" +
                        "\t\t\tSELECT\n" +
                        "\t\t\t\tquestionnaire_id,\n" +
                        "\t\t\t\tdoctor_id,\n" +
                        "\t\t\t\tdoctor_name,\n" +
                        "\t\t\t\tcount(survey_status) AS survey_status1\n" +
                        "\t\t\tFROM\n" +
                        "\t\t\t\tpatient_satisfaction_survey\n" +
                        "\t\t\tWHERE\n" +
                        "\t\t\t\tdoctor_id IS NOT NULL\n" +
                        "\t\t\tAND survey_status = 1 ");
                if (StringUtils.isNotEmpty(request.getBeginDate())) {
                    stringBuffer.append("and notify_time>=:beginDate  ");
                }
                if (StringUtils.isNotEmpty(request.getEndDate())) {
                    stringBuffer.append("and notify_time<=:endDate  ");
                }
                if (StringUtils.isNotEmpty(request.getQuestionnaireId())){
                    stringBuffer.append("and questionnaire_id=:questionnaire_id ");
                }

                stringBuffer.append("GROUP BY\n" +
                        "\t\t\t\tdoctor_id\n" +
                        "\t\t) f ON t.doctor_id = f.doctor_id\n" +
                        "\t\tLEFT JOIN (\n" +
                        "\t\t\tSELECT\n" +
                        "\t\t\t\tcount(*) abc,\n" +
                        "\t\t\t\tdoctor_id\n" +
                        "\t\t\tFROM\n" +
                        "\t\t\t\tpatient_satisfaction_survey\n" +
                        "\t\t\tWHERE\n" +
                        "\t\t\t\t1 = 1 #and survey_status = 1\n" +
                        "\t\t\tAND survey_score >= 8\n" +
                        "\t\t\tAND survey_score <= 10 ");
                if (StringUtils.isNotEmpty(request.getBeginDate())) {
                    stringBuffer.append(" and notify_time>=:beginDate  ");
                }
                if (StringUtils.isNotEmpty(request.getEndDate())) {
                    stringBuffer.append("and notify_time<=:endDate  ");
                }
                if (StringUtils.isNotEmpty(request.getQuestionnaireId())){
                    stringBuffer.append("and questionnaire_id=:questionnaire_id ");
                }
                stringBuffer.append(" GROUP BY\n" +
                        "\t\t\t\tdoctor_id\n" +
                        "\t\t) h ON t.doctor_id = h.doctor_id\n" +
                        "\t\tWHERE\n" +
                        "\t\t\t1 = 1 ");

                if (StringUtils.isNotEmpty(request.getQuestionnaireId())){
                    stringBuffer.append("and t.questionnaire_id=:questionnaire_id ");
                }
                stringBuffer.append("\tORDER BY\n" +
                        "\t\t\tc\n" +
                        "\t) dd" );
                Query sqlQuery = ss.createSQLQuery(stringBuffer.toString());

                if (StringUtils.isNotEmpty(request.getBeginDate())) {
                    sqlQuery.setParameter("beginDate", request.getBeginDate() + " 00:00:00");
                }
                if (StringUtils.isNotEmpty(request.getEndDate())) {
                    sqlQuery.setParameter("endDate", request.getEndDate() + " 23:59:59");
                }
                if (CommonUtils.isNotEmpty(request.getQuestionnaireId())) {
                    sqlQuery.setParameter("questionnaire_id", request.getQuestionnaireId());
                }
                log.info("-----sqlQuery-------"+sqlQuery);
                sqlQuery.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);

                List<Map> list = sqlQuery.list();
                List<QueryDoctorTotalResponse> voList = new ArrayList<>();
                if (CommonUtils.isNotEmpty(list)) {
                    for (Map map : list) {
                        voList.add(ConversionUtils.convert(map, QueryDoctorTotalResponse.class));
                    }
                } else {
                    voList = new ArrayList<>();
                }
                setResult(voList.get(0));
            }

        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();

    }

    public QueryDeptTotalResponse queryDeptTotal(QuerySurveyDeptListRequest request) {


        HibernateStatelessResultAction<QueryDeptTotalResponse> action = new AbstractHibernateStatelessResultAction<QueryDeptTotalResponse>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer stringBuffer= new StringBuffer();
                stringBuffer.append(" SELECT\n" +
                        "\tsum(dd.push) as totalPush,\n" +
                        "\tsum(dd.back) as totalBack  ,\n" +
                        "\tsum(dd.myl) / sum(dd.back)  as totalSum\n" +
                        "FROM\n" +
                        "\t(\n" +
                        "\t\tSELECT\n" +
                        "\t\t\tt.questionnaire_id,\n" +
                        "\t\t\tt.department_id AS departmentId,\n" +
                        "\t\t\tt.department_name AS departmentName,\n" +
                        "\t\t\tt.survey_status AS push,\n" +
                        "\t\t\tf.survey_status1 AS back,\n" +
                        "\t\t\th.abc AS myl,\n" +
                        "\t\t\tIFNULL(h.abc, 0) / f.survey_status1 AS c,\n" +
                        "\t\t\tt.type\n" +
                        "\t\tFROM\n" +
                        "\t\t\t(\n" +
                        "\t\t\t\tSELECT\n" +
                        "\t\t\t\t\tquestionnaire_id,\n" +
                        "\t\t\t\t\tdepartment_id,\n" +
                        "\t\t\t\t\tdepartment_name,\n" +
                        "\t\t\t\t\tCASE\n" +
                        "\t\t\t\tWHEN INSTR(department_name, '东院') > 0 THEN\n" +
                        "\t\t\t\t\t'1'\n" +
                        "\t\t\t\tELSE\n" +
                        "\t\t\t\t\t'2'\n" +
                        "\t\t\t\tEND AS 'type',\n" +
                        "\t\t\t\tcount(survey_status) AS survey_status\n" +
                        "\t\t\tFROM\n" +
                        "\t\t\t\tpatient_satisfaction_survey\n" +
                        "\t\t\tWHERE\n" +
                        "\t\t\t\tdepartment_id IS NOT NULL\n" +
                        "\t\t\tAND notify_status = 1 ");


                if (StringUtils.isNotEmpty(request.getBeginDate())) {
                    stringBuffer.append(" and notify_time>=:beginDate  ");
                }
                if (StringUtils.isNotEmpty(request.getEndDate())) {
                    stringBuffer.append(" and notify_time<=:endDate  ");
                }


                stringBuffer.append(" GROUP BY\n" +
                        "\t\t\t\tdepartment_id\n" +
                        "\t\t\t) t\n" +
                        "\t\tLEFT JOIN (\n" +
                        "\t\t\tSELECT\n" +
                        "\t\t\t\tquestionnaire_id,\n" +
                        "\t\t\t\tdepartment_id,\n" +
                        "\t\t\t\tdepartment_name,\n" +
                        "\t\t\t\tcount(survey_status) AS survey_status1\n" +
                        "\t\t\tFROM\n" +
                        "\t\t\t\tpatient_satisfaction_survey\n" +
                        "\t\t\tWHERE\n" +
                        "\t\t\t\tdepartment_id IS NOT NULL\n" +
                        "\t\t\tAND survey_status = 1 ");
                if (StringUtils.isNotEmpty(request.getBeginDate())) {
                    stringBuffer.append(" and notify_time>=:beginDate  ");
                }
                if (StringUtils.isNotEmpty(request.getEndDate())) {
                    stringBuffer.append(" and notify_time<=:endDate  ");
                }


                stringBuffer.append(" GROUP BY\n" +
                        "\t\t\t\tdepartment_id\n" +
                        "\t\t) f ON t.department_id = f.department_id\n" +
                        "\t\tLEFT JOIN (\n" +
                        "\t\t\tSELECT\n" +
                        "\t\t\t\tcount(*) abc,\n" +
                        "\t\t\t\tdepartment_id\n" +
                        "\t\t\tFROM\n" +
                        "\t\t\t\tpatient_satisfaction_survey\n" +
                        "\t\t\tWHERE\n" +
                        "\t\t\t\t1 = 1 #and survey_status = 1\n" +
                        "\t\t\tAND survey_score >= 8\n" +
                        "\t\t\tAND survey_score <= 10 ");
                if (StringUtils.isNotEmpty(request.getBeginDate())) {
                    stringBuffer.append(" and notify_time>=:beginDate  ");
                }
                if (StringUtils.isNotEmpty(request.getEndDate())) {
                    stringBuffer.append(" and notify_time<=:endDate  ");
                }
                if (StringUtils.isNotEmpty(request.getQuestionnaireId())){
                    stringBuffer.append("  and questionnaire_id=:questionnaire_id ");
                }
                stringBuffer.append(" GROUP BY\n" +
                        "\t\t\t\tdepartment_id\n" +
                        "\t\t) h ON t.department_id = h.department_id\n" +
                        "\t\tWHERE\n" +
                        "\t\t\t1 = 1 ");

                if (StringUtils.isNotEmpty(request.getQuestionnaireId())){
                    stringBuffer.append(" and t.questionnaire_id=:questionnaire_id ");
                }
                stringBuffer.append("ORDER BY\n" +
                        "\t\t\ttype,\n" +
                        "\t\t\tc\n" +
                        "\t) dd");
                Query sqlQuery = ss.createSQLQuery(stringBuffer.toString());

                if (StringUtils.isNotEmpty(request.getBeginDate())) {
                    sqlQuery.setParameter("beginDate", request.getBeginDate() + " 00:00:00");
                }
                if (StringUtils.isNotEmpty(request.getEndDate())) {
                    sqlQuery.setParameter("endDate", request.getEndDate() + " 23:59:59");
                }
                if (CommonUtils.isNotEmpty(request.getQuestionnaireId())) {
                    sqlQuery.setParameter("questionnaire_id", request.getQuestionnaireId());
                }
                log.info("-----sqlQuery-------"+sqlQuery);
                sqlQuery.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);

                List<Map> list = sqlQuery.list();
                List<QueryDeptTotalResponse> voList = new ArrayList<>();
                if (CommonUtils.isNotEmpty(list)) {
                    for (Map map : list) {
                        voList.add(ConversionUtils.convert(map, QueryDeptTotalResponse.class));
                    }
                } else {
                    voList = new ArrayList<>();
                }
                setResult(voList.get(0));
            }

        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    public List<SurveyPlanDeptListResponse> querySurveyDeptByDeptIdNew( final  QuerySurveyDeptListRequest req) {
        HibernateStatelessResultAction<List<SurveyPlanDeptListResponse>> action =
                new AbstractHibernateStatelessResultAction<List<SurveyPlanDeptListResponse>>() {
                    @Override
                    public void execute(StatelessSession ss) {
                        StringBuffer stringBuffer = new StringBuffer();
                        stringBuffer.append(" SELECT\n" +
                                "\tt.questionnaire_id,\n" +
                                "\tt.department_id AS departmentId,\n" +
                                "\tt.department_name AS departmentName,\n" +
                                "\tt.survey_status AS push,\n" +
                                "\tf.survey_status1 AS back,\n" +
                                "\tIFNULL(h.abc, 0) / f.survey_status1 AS c,\n" +
                                "  IFNULL(h1.abc, 0) / f.survey_status1 AS d,\n" +
                                "  IFNULL(h2.abc, 0) / f.survey_status1 AS e,\n" +
                                "\tt.type\n" +
                                "FROM\n" +
                                "\t(\n" +
                                "\t\tSELECT\n" +
                                "\t\t\tquestionnaire_id,\n" +
                                "\t\t\tdepartment_id,\n" +
                                "\t\t\tdepartment_name,\n" +
                                "\t\t\tCASE\n" +
                                "\t\tWHEN INSTR(department_name, '东院') > 0 THEN\n" +
                                "\t\t\t'1'\n" +
                                "\t\tELSE\n" +
                                "\t\t\t'2'\n" +
                                "\t\tEND AS 'type',\n" +
                                "\t\tcount(survey_status) AS survey_status\n" +
                                "\tFROM\n" +
                                "\t\tpatient_satisfaction_survey\n" +
                                "\tWHERE\n" +
                                "\t\tdepartment_id IS NOT NULL\n" +
                                "\tAND notify_status = 1  ");
                           if(StringUtils.isNotEmpty(req.getBeginDate())){
                               stringBuffer.append(" and notify_time>=:beginDate ");

                           }
                           if (StringUtils.isNotEmpty(req.getEndDate())){
                               stringBuffer.append("and notify_time<=:endDate  ");
                           }
                           stringBuffer.append(" \tGROUP BY\n" +
                                   "\t\tdepartment_id\n" +
                                   "\t) t\n" +
                                   "LEFT JOIN (\n" +
                                   "\tSELECT\n" +
                                   "\t\tquestionnaire_id,\n" +
                                   "\t\tdepartment_id,\n" +
                                   "\t\tdepartment_name,\n" +
                                   "\t\tcount(survey_status) AS survey_status1\n" +
                                   "\tFROM\n" +
                                   "\t\tpatient_satisfaction_survey\n" +
                                   "\tWHERE\n" +
                                   "\t\tdepartment_id IS NOT NULL\n" +
                                   "\tAND survey_status = 1  ");


                        if (StringUtils.isNotEmpty(req.getBeginDate())) {
                            stringBuffer.append(" and notify_time>=:beginDate  ");
                        }
                        if (StringUtils.isNotEmpty(req.getEndDate())) {
                            stringBuffer.append(" and notify_time<=:endDate  ");
                        }

                        stringBuffer.append("  GROUP BY\n" +
                                "    department_id\n" +
                                ") f ON t.department_id = f.department_id\n" +
                                "LEFT JOIN (\n" +
                                "  SELECT\n" +
                                "    count(*) abc,\n" +
                                "    department_id\n" +
                                "  FROM\n" +
                                "    patient_satisfaction_survey\n" +
                                "  WHERE\n" +
                                "    1 = 1\n" +
                                "    and department_id IS NOT NULL\n" +
                                "   and survey_status = 1\n" +
                                "  AND survey_score >= 8\n" +
                                "  AND survey_score <= 10   ");

                        if (StringUtils.isNotEmpty(req.getBeginDate())) {
                            stringBuffer.append(" and notify_time>=:beginDate  ");
                        }
                        if (StringUtils.isNotEmpty(req.getEndDate())) {
                            stringBuffer.append(" and notify_time<=:endDate  ");
                        }
                        if (CommonUtils.isNotEmpty(req.getQuestionnaireId())) {
                            stringBuffer.append(" and questionnaire_id =:questionnaire_id  ");
                        }

                        stringBuffer.append(" \tGROUP BY\n" +
                                "\t\tdepartment_id\n" +
                                ") h ON t.department_id = h.department_id\n" +
                                "LEFT JOIN (\n" +
                                "\tSELECT\n" +
                                "\t\tcount(*) AS abc,\n" +
                                "\t\tdepartment_id\n" +
                                "\tFROM\n" +
                                "\t\tpatient_satisfaction_survey\n" +
                                "\tWHERE\n" +
                                "\t\t1 = 1\n" +
                                "\tAND department_id IS NOT NULL\n" +
                                "\tAND survey_status = 1\n" +
                                "\tAND survey_score >= 4\n" +
                                "\tAND survey_score <= 7 ");
                        if (StringUtils.isNotEmpty(req.getBeginDate())) {
                            stringBuffer.append(" and notify_time>=:beginDate  ");
                        }
                        if (StringUtils.isNotEmpty(req.getEndDate())) {
                            stringBuffer.append(" and notify_time<=:endDate  ");
                        }
                        if (CommonUtils.isNotEmpty(req.getQuestionnaireId())) {
                            stringBuffer.append(" and questionnaire_id =:questionnaire_id  ");
                        }
                        stringBuffer.append(" GROUP BY\n" +
                                "\t\tdepartment_id\n" +
                                ") h1 ON t.department_id = h1.department_id\n" +
                                "LEFT JOIN\n" +
                                "(\n" +
                                "\tSELECT\n" +
                                "\t\tcount(*) AS abc,\n" +
                                "\t\tdepartment_id\n" +
                                "\tFROM\n" +
                                "\t\tpatient_satisfaction_survey\n" +
                                "\tWHERE\n" +
                                "\t\t1 = 1\n" +
                                "\tAND department_id IS NOT NULL\n" +
                                "\tAND survey_status = 1\n" +
                                "\tAND survey_score >= 1\n" +
                                "\tAND survey_score <= 3 ");
                        if (StringUtils.isNotEmpty(req.getBeginDate())) {
                            stringBuffer.append(" and notify_time>=:beginDate  ");
                        }
                        if (StringUtils.isNotEmpty(req.getEndDate())) {
                            stringBuffer.append(" and notify_time<=:endDate  ");
                        }
                        if (CommonUtils.isNotEmpty(req.getQuestionnaireId())) {
                            stringBuffer.append(" and questionnaire_id =:questionnaire_id  ");
                        }
                        stringBuffer.append(" \n" +
                                "\tGROUP BY\n" +
                                "\t\tdepartment_id\n" +
                                ") h2 ON t.department_id = h2.department_id\n" +
                                "\n" +
                                "WHERE\n" +
                                "\t1 = 1 ");

//
//                        stringBuffer.append(" GROUP BY\n" +
//                                "    department_id\n" +
//                                ") h ON t.department_id = h.department_id\n" +
//                                "WHERE\n" +
//                                "  1 = 1 ");
//

                        if (CommonUtils.isNotEmpty(req.getQuestionnaireId())) {
                            stringBuffer.append(" and t.questionnaire_id =:questionnaire_id  ");
                        }
//

                        if (CommonUtils.isNotEmpty(req.getDeptId())) {
                            stringBuffer.append("and t.department_id =:department_id ");
                        }

                        stringBuffer.append( " ORDER BY type, c ");
                        Query sqlQuery = ss.createSQLQuery(stringBuffer.toString());

                        if (CommonUtils.isNotEmpty(req.getDeptId())) {
                            sqlQuery.setParameter("department_id", req.getDeptId());
                        }

                        if (StringUtils.isNotEmpty(req.getBeginDate())) {
                            sqlQuery.setParameter("beginDate", req.getBeginDate() + " 00:00:00");
                        }
                        if (StringUtils.isNotEmpty(req.getEndDate())) {
                            sqlQuery.setParameter("endDate", req.getEndDate() + " 23:59:59");
                        }
                        if (CommonUtils.isNotEmpty(req.getQuestionnaireId())) {
                            sqlQuery.setParameter("questionnaire_id", req.getQuestionnaireId());
                        }

                        sqlQuery.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);

                        List<Map> list = sqlQuery.list();
                        List<SurveyPlanDeptListResponse> voList = new ArrayList<>();
                        if (CommonUtils.isNotEmpty(list)) {
                            for (Map map : list) {
                                voList.add(ConversionUtils.convert(map, SurveyPlanDeptListResponse.class));
                            }
                        } else {
                            voList = new ArrayList<>();
                        }
                        setResult(voList);
                    }
                };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();


    }

    public List<SurveyPlanDeptListResponse> queryDoctorWorkListNew(QuerySurveyDeptListRequest req) {


        HibernateStatelessResultAction<List<SurveyPlanDeptListResponse>> action =
                new AbstractHibernateStatelessResultAction<List<SurveyPlanDeptListResponse>>() {
                    @Override
                    public void execute(StatelessSession ss) {
                        StringBuffer stringBuffer = new StringBuffer();

                        stringBuffer.append(" SELECT\n" +
                                "\tt.questionnaire_id,\n" +
                                "\tt.doctor_id AS doctorId,\n" +
                                "\tt.doctor_name AS doctorName,\n" +
                                "\tt.survey_status AS push,\n" +
                                "\tf.survey_status1 AS back,\n" +
                                "\tIFNULL(h.abc, 0) / f.survey_status1 AS c,\n" +
                                "  IFNULL(h1.abc,0) / f.survey_status1  as d,\n" +
                                "  IFNULL(h2.abc,0) / f.survey_status1  as e\n" +
                                "FROM\n" +
                                "\t(\n" +
                                "\t\tSELECT\n" +
                                "\t\t\tquestionnaire_id,\n" +
                                "\t\t\tdoctor_id,\n" +
                                "\t\t\tdoctor_name,\n" +
                                "\t\t\tcount(survey_status) AS survey_status\n" +
                                "\t\tFROM\n" +
                                "\t\t\tpatient_satisfaction_survey\n" +
                                "\t\tWHERE\n" +
                                "\t\t\tdoctor_id IS NOT NULL  ");
                                                if(StringUtils.isNotEmpty(req.getDoctorName())){
                            stringBuffer.append("and doctor_name=:doctor_name ");
                        }
                        if (StringUtils.isNotEmpty(req.getBeginDate())) {
                            stringBuffer.append("and notify_time>=:beginDate  ");
                        }
                        if (StringUtils.isNotEmpty(req.getEndDate())) {
                            stringBuffer.append("and notify_time<=:endDate  ");
                        }
                        if (StringUtils.isNotEmpty(req.getQuestionnaireId())){
                            stringBuffer.append("and questionnaire_id=:questionnaire_id ");
                        }
                        stringBuffer.append(" \tGROUP BY\n" +
                                "\t\t\tdoctor_id\n" +
                                "\t) t\n" +
                                "LEFT JOIN (\n" +
                                "\tSELECT\n" +
                                "\t\tquestionnaire_id,\n" +
                                "\t\tdoctor_id,\n" +
                                "\t\tdoctor_name,\n" +
                                "\t\tcount(survey_status) AS survey_status1\n" +
                                "\tFROM\n" +
                                "\t\tpatient_satisfaction_survey\n" +
                                "\tWHERE\n" +
                                "\t\tdoctor_id IS NOT NULL\n" +
                                "\tAND survey_status = 1  ");
                        if (StringUtils.isNotEmpty(req.getBeginDate())) {
                            stringBuffer.append("and notify_time>=:beginDate  ");
                        }
                        if (StringUtils.isNotEmpty(req.getEndDate())) {
                            stringBuffer.append("and notify_time<=:endDate  ");
                        }
                        if (StringUtils.isNotEmpty(req.getQuestionnaireId())){
                            stringBuffer.append("and questionnaire_id=:questionnaire_id ");
                        }
                        stringBuffer.append(" \n" +
                                "\tGROUP BY\n" +
                                "\t\tdoctor_id\n" +
                                ") f ON t.doctor_id = f.doctor_id\n" +
                                "LEFT JOIN (\n" +
                                "\tSELECT\n" +
                                "\t\tcount(*) AS abc,\n" +
                                "\t\tdoctor_id\n" +
                                "\tFROM\n" +
                                "\t\tpatient_satisfaction_survey\n" +
                                "\tWHERE\n" +
                                "\t\t1 = 1 #and survey_status = 1\n" +
                                "\tAND survey_score >= 8\n" +
                                "\tAND survey_score <= 10 ");
                        if (StringUtils.isNotEmpty(req.getBeginDate())) {
                            stringBuffer.append("and notify_time>=:beginDate  ");
                        }
                        if (StringUtils.isNotEmpty(req.getEndDate())) {
                            stringBuffer.append("and notify_time<=:endDate  ");
                        }
                        if (StringUtils.isNotEmpty(req.getQuestionnaireId())){
                            stringBuffer.append("and questionnaire_id=:questionnaire_id ");
                        }

                        stringBuffer.append(" \n" +
                                "\tGROUP BY\n" +
                                "\t\tdoctor_id\n" +
                                ") h ON t.doctor_id = h.doctor_id\n" +
                                "LEFT JOIN (\n" +
                                "\tSELECT\n" +
                                "\t\tcount(*) AS abc,\n" +
                                "\t\tdoctor_id\n" +
                                "\tFROM\n" +
                                "\t\tpatient_satisfaction_survey\n" +
                                "\tWHERE\n" +
                                "\t\t1 = 1 #and survey_status = 1\n" +
                                "\tAND survey_score > 3\n" +
                                "\tAND survey_score < 8 ");
                        if (StringUtils.isNotEmpty(req.getBeginDate())) {
                            stringBuffer.append("and notify_time>=:beginDate  ");
                        }
                        if (StringUtils.isNotEmpty(req.getEndDate())) {
                            stringBuffer.append("and notify_time<=:endDate  ");
                        }
                        if (StringUtils.isNotEmpty(req.getQuestionnaireId())){
                            stringBuffer.append("and questionnaire_id=:questionnaire_id ");
                        }
                        stringBuffer.append("GROUP BY\n" +
                                "\t\tdoctor_id\n" +
                                ") h1 ON t.doctor_id = h1.doctor_id\n" +
                                "\n" +
                                "LEFT JOIN (\n" +
                                "\tSELECT\n" +
                                "\t\tcount(*) AS abc,\n" +
                                "\t\tdoctor_id\n" +
                                "\tFROM\n" +
                                "\t\tpatient_satisfaction_survey\n" +
                                "\tWHERE\n" +
                                "\t\t1 = 1 #and survey_status = 1\n" +
                                "\tAND survey_score >= 0\n" +
                                "\tAND survey_score <= 3 ");
                        if (StringUtils.isNotEmpty(req.getBeginDate())) {
                            stringBuffer.append("and notify_time>=:beginDate  ");
                        }
                        if (StringUtils.isNotEmpty(req.getEndDate())) {
                            stringBuffer.append("and notify_time<=:endDate  ");
                        }
                        if (StringUtils.isNotEmpty(req.getQuestionnaireId())){
                            stringBuffer.append("and questionnaire_id=:questionnaire_id ");
                        }

                        stringBuffer.append(" GROUP BY\n" +
                                "\t\tdoctor_id\n" +
                                ") h2 ON t.doctor_id = h2.doctor_id\n" +
                                "\n" +
                                "\n" +
                                "\n" +
                                "\n" +
                                "WHERE\n" +
                                "\t1 = 1 ");
                        if (StringUtils.isNotEmpty(req.getQuestionnaireId())){
                            stringBuffer.append("and t.questionnaire_id=:questionnaire_id  ");
                        }
                        stringBuffer.append("ORDER BY\n" +
                                "\tc ");
//                        stringBuffer.append( "SELECT\n" +
//                                "\tt.questionnaire_id,\n" +
//                                "\tt.doctor_id AS doctorId,\n" +
//                                "\tt.doctor_name AS doctorName,\n" +
//                                "\tt.survey_status AS push ,\n" +
//                                "\tf.survey_status1 AS back,\n" +
//                                "\t#t.survey_status / f.survey_status1 AS c,\n" +
//                                "\tIFNULL(h.abc, 0) / f.survey_status1 AS c\n" +
//                                "FROM\n" +
//                                "\t(\n" +
//                                "\t\tSELECT\n" +
//                                "\t\t\tquestionnaire_id,\n" +
//                                "\t\t\tdoctor_id,\n" +
//                                "\t\t\tdoctor_name,\n" +
//                                "\t\t\tcount(survey_status) AS survey_status\n" +
//                                "\t\tFROM\n" +
//                                "\t\t\tpatient_satisfaction_survey\n" +
//                                "\t\tWHERE\n" +
//                                "\t\t\tdoctor_id IS NOT NULL ");

////                        if (StringUtils.isNotEmpty(req.getDoctorName())){
////                            stringBuffer.append("and doctor_name=:doctor_name ");
////                        }
//                        stringBuffer.append(" GROUP BY\n" +
//                                "\t\t\tdoctor_id\n" +
//                                "\t) t\n" +
//                                "LEFT JOIN (\n" +
//                                "\tSELECT\n" +
//                                "\t\tquestionnaire_id,\n" +
//                                "\t\tdoctor_id,\n" +
//                                "\t\tdoctor_name,\n" +
//                                "\t\tcount(survey_status) AS survey_status1\n" +
//                                "\tFROM\n" +
//                                "\t\tpatient_satisfaction_survey\n" +
//                                "\tWHERE\n" +
//                                "\t\tdoctor_id IS NOT NULL\n" +
//                                "  AND survey_status = 1 ");
//
//
//                        if (StringUtils.isNotEmpty(req.getBeginDate())) {
//                            stringBuffer.append("and notify_time>=:beginDate  ");
//                        }
//                        if (StringUtils.isNotEmpty(req.getEndDate())) {
//                            stringBuffer.append("and notify_time<=:endDate  ");
//                        }
//                        if (StringUtils.isNotEmpty(req.getQuestionnaireId())){
//                            stringBuffer.append("and questionnaire_id=:questionnaire_id  ");
//                        }
//
//                        stringBuffer.append(" GROUP BY\n" +
//                                "\t\tdoctor_id\n" +
//                                ") f ON t.doctor_id = f.doctor_id\n" +
//                                "LEFT JOIN (\n" +
//                                "\tSELECT\n" +
//                                "\t\tcount(*) abc,\n" +
//                                "\t\tdoctor_id\n" +
//                                "\tFROM\n" +
//                                "\t\tpatient_satisfaction_survey\n" +
//                                "\tWHERE\n" +
//                                "\t\t1 = 1 #and survey_status = 1\n" +
//                                "\tAND survey_score >= 8\n" +
//                                "\tAND survey_score <= 10  ");
//                        if (StringUtils.isNotEmpty(req.getBeginDate())) {
//                            stringBuffer.append("and notify_time>=:beginDate  ");
//                        }
//                        if (StringUtils.isNotEmpty(req.getEndDate())) {
//                            stringBuffer.append("and notify_time<=:endDate  ");
//                        }
//                        if (StringUtils.isNotEmpty(req.getQuestionnaireId())) {
//                            stringBuffer.append("and questionnaire_id=:questionnaire_id  ");
//
//                        }
//                        stringBuffer.append("GROUP BY\n" +
//                                "\t\tdoctor_id\n" +
//                                ") h ON t.doctor_id = h.doctor_id\n" +
//                                "WHERE\n" +
//                                "\t1 = 1 ");



                        Query sqlQuery = ss.createSQLQuery(stringBuffer.toString());

//                        if (CommonUtils.isNotEmpty(req.getDoctorId())) {
//                            sqlQuery.setParameter("doctor_id", req.getDoctorId());
//                        }
//                        if (CommonUtils.isNotEmpty(req.getDeptId())) {
////                            String trim = Utils.trim(StrUtil.join(",", req.getNewDeptId()).replaceAll("\\b", "'"), ",");
////                            log.info("==============>"+trim);
//                            sqlQuery.setParameterList("department_id", req.getNewDeptId());
//                        }

                        if (StringUtils.isNotEmpty(req.getBeginDate())) {
                            sqlQuery.setParameter("beginDate", req.getBeginDate() + " 00:00:00");
                        }
                        if (StringUtils.isNotEmpty(req.getEndDate())) {
                            sqlQuery.setParameter("endDate", req.getEndDate() + " 23:59:59");
                        }
                        if (CommonUtils.isNotEmpty(req.getQuestionnaireId())) {
                            sqlQuery.setParameter("questionnaire_id", req.getQuestionnaireId());
                        }
                        if (CommonUtils.isNotEmpty(req.getDoctorName())){
                            sqlQuery.setParameter("doctor_name",req.getDoctorName());
                        }
//                        if (CommonUtils.isNotEmpty(req.getDoctorName())){
//                            sqlQuery.setParameter("doctor_name",req.getDoctorName());
//                        }
                        log.info("-----sqlQuery-------"+sqlQuery);
                        sqlQuery.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);

                        List<Map> list = sqlQuery.list();
                        List<SurveyPlanDeptListResponse> voList = new ArrayList<>();
                        if (CommonUtils.isNotEmpty(list)) {
                            for (Map map : list) {
                                voList.add(ConversionUtils.convert(map, SurveyPlanDeptListResponse.class));
                            }
                        } else {
                            voList = new ArrayList<>();
                        }
                        setResult(voList);
                    }
                };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();


    }
}
